Oracle using indexes

From: Christ Follower <christ_follower_at_my-deja.com>
Date: Mon, 07 Feb 2000 19:01:35 GMT
Message-ID: <87n4qb$c8$1@nnrp1.deja.com>

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 Mon Feb 07 2000 - 13:01:35 CST