Re: Index Range Scans....

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 3 Mar 2006 15:38:12 +0000 (UTC)
Message-ID: <du9nt4$f3d$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com>

"Miggins" <mtproc_at_yahoo.co.uk> wrote in message news:1141391689.633706.102630_at_z34g2000cwc.googlegroups.com...
> 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
>

You've obviously got the CBO running,
but which version of Oracle. If it's 9i then your assumption about unique key
would be correct - the values cross the
join condition by transitive closure - I've just run up a little test case with the following result:

|   0 | SELECT STATEMENT     |                 |     1 |    66 |     2 |
|   1 |  NESTED LOOPS        |                 |     1 |    66 |     2 |
|*  2 |   INDEX UNIQUE SCAN  | LOG_DETAILS_PK  |     1 |    33 |     1 |
|*  3 |   INDEX RANGE SCAN   | LOG_PK          |     1 |    33 |     1 |
------------------------------------------------------------------------

Predicate Information (identified by operation id):

2 - access("LD"."F1"=1139 AND "LD"."F2"=21 AND "LD"."F3"='Z') 3 - access("L"."F1"=1139 AND "L"."F2"=21)

Since this is not happening with your query, there seem to be two possibilities:
a) you are using a version 8i which does not

support the necessary transitive closure.

b) your query is not quite what you thought - possibly due to column mismatch, or a simple typing error.

If you are on 9i, run the query through dbms_xplan and check the filter and access predicates.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

Received on Fri Mar 03 2006 - 09:38:12 CST