Re: Oracle using indexes

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.
Received on Wed Feb 09 2000 - 20:43:46 CST