Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!colt.net!peernews3.colt.net!newsfeed.stueberl.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: "Robert Klemme" <bob.news@gmx.net>
Newsgroups: comp.databases.oracle.server
Subject: Re: Index Range Scans....
Date: Fri, 3 Mar 2006 15:14:06 +0100
Lines: 53
Message-ID: <46r19kFc2r87U1@individual.net>
References: <1141391689.633706.102630@z34g2000cwc.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain;
 charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Trace: individual.net 5URHPHYWhO0Ab4Wh1rvJnQhXZ961UNpii0P4aKpFEmp8gPSH0=
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1506
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262693

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