Re: Oracle using indexes

From: Christopher M. Day <christopher.day_at_rdbms.freeserve.co.uk>
Date: Sun, 06 Feb 2000 22:41:40 +0000
Message-ID: <389DF8A4.C0574537@rdbms.freeserve.co.uk>

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