Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index Range Scans....
"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.htmlReceived on Fri Mar 03 2006 - 09:38:12 CST