| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle using indexes
You didn't include the results of your explain plan but I would guess
that the join is accomplished by using a Nested Loop join. If that is
the case, then the second table in your FROM clause is the driving table
and the driving table needs to have a full table scan performed on it.
If TBL1 is smaller than TBL2, then reverse the order in the FROM clause.
HTH,
Brian
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.
--
 |
 |