Re: Oracle using indexes

It is rule based by inquiring v$parameter.

In article <38A225E1.125AE759_at_www.cis.com.ph>,   Michael Puente <mcpuente_at_www.cis.com.ph> wrote:
> I think your using cost-based optimizer.
> If the tables are small ( a few blocks ), it faster to do a full-table
> scan. Also in using Cost-based approach Oracle uses the index only
if the
> rows returned are less than 10 % (value differ between versions).
>
> Christ Follower wrote:
>
> > I use explain plan to verify how Oracle utilizes indexes and found
an
> > interesting scenario.
> > CREATE TBL1 (
> > KEY1 VARCHAR2(10) PRIMARY KEY,
> > FLD2 VARCHAR2(5));
> > CREATE TBL2 (
> > KEY1 VARCHAR2(10) PRIMARY KEY,
> > FLD2 VARCHAR2(5));
> >
> > EXPLAIN PLAN SET STATEMENT_ID='TEST' INTO PLAN_TABLE FOR
> > SELECT A.KEY1
> > FROM TBL1 A, TBL2 B
> > WHERE A.KEY1 = B.KEY1
> >
> > It reveals Oracle is performing a full table scan on TBL2. I wonder
why
> > Oracle not using the primary key index since the values in
TBL2.KEY1 is
> > already in the index (smaller than scanning the entire table).
> >
> > Comment?
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Feb 10 2000 - 13:05:56 CST