Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index Range Scans....

Re: Index Range Scans....

From: Robert Klemme <bob.news_at_gmx.net>
Date: Fri, 3 Mar 2006 15:14:06 +0100
Message-ID: <46r19kFc2r87U1@individual.net>


Miggins wrote:
> Each,
>
> I have the following two tables.....
>
> LOGS
> F1 number(8)
> F2 number(8)
> F3 varchar2(10)
>
> This table has a primary key composed of the first 3 fields
>
> LOGDETAILS
> F1 number(8)
> F2 number(8)
> F3 varchar2(3)
>
> Again primary key based on all 3 fields.....
>
> select L.f1, L.f2, L.f3, LD.f1, LD.f2, LD.f3
> from LOGS L,
> LOG_DETAILS LD
> where L.f1 = 1139
> and L.f2 = 21
> and LD.f1 = L.F1
> and LD.f2 = L.F2
> and LD.f3 = 'Z'
>
> When i run the explain plan i get the following results
>

>>   0 | SELECT STATEMENT             |
>>   1 |  HASH JOIN                   |
>>   2 |   TABLE ACCESS BY INDEX ROWID| LOG_DETAILS
>>   3 |    INDEX RANGE SCAN          | LOG_DETAILS_K
>>   4 |   TABLE ACCESS BY INDEX ROWID| LOGS
>>   5 |    INDEX RANGE SCAN          | LOGS_PK

>
> I can see why it does an index range scan on the LOGS table but why
> does it also do an index range scan on the LOG_DETAILS table when the
> full primary key value is known. I would have thought it would have
> done a unique scan.
>
> Any help greatly appreciated

Maybe because you forgot

and LD.f3 = L.F3

?

    robert Received on Fri Mar 03 2006 - 08:14:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US