Re: Oracle using indexes

From: Christ Follower <christ_follower_at_my-deja.com>
Date: Wed, 09 Feb 2000 00:21:49 GMT
Message-ID: <87qbun$c72$1@nnrp1.deja.com>

The optimizer is Rule based. I tried the hints as suggested but it just changed to have full scan on the other table, TBL1, and uses the index on TBL2.

In article <87n4qb$c8$1_at_nnrp1.deja.com>, Christ Follower <christ_follower_at_my-deja.com> 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 Tue Feb 08 2000 - 18:21:49 CST