Index Range Scans....

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 Received on Fri Mar 03 2006 - 07:23:28 CST