Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle using indexes
Christ,
Have you analysed your tables and indexes ? Is it quicker that using the index on TBL1? (apply using a hint)
Chris
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 Sun Feb 06 2000 - 16:41:40 CST