Composite Index Problem

[oraboy]

Given this query:

select distinct ris.officer_id
from relationship_info_snapshot ris
where ris.absolute_period = 13
and ris.officer_id = '010001'
and ris.entity_level = 1

where there is a composite index on the table for absolute_period and officer_id

I get the following explain plan:

SELECT STATEMENT
SORT (UNIQUE NOSORT)
TABLE ACCESS (FULL)--RELATIONSHIP_INFO_SNAPSHOT

When I execute the query removing the last condition as:

select distinct ris.officer_id
from relationship_info_snapshot ris
where ris.absolute_period = 13
and ris.officer_id = '010001'

I get this explain plan:

SELECT STATEMENT
SORT (UNIQUE NOSORT)
INDEX (FAST FULL SCAN)--ABSOLUTE_PERIOD_OFFICER_X (NON-UNIQUE)

There are 12 distinct values in the index and the table has 200,000 rows. I've done analyze on the table and index.

I don't understand why adding the last condition causes Oracle to do a full scan instead of using the index??

Thanks

[Oracledba8]

Hi,

I been having this trouble many times. Some time Oracle understand that Full scan is faster than using the Index. The reason is the the Density that the Statistics Produce while you analyze.

To me it was logical not to use the Index.

Thanks..

[macswell]

Hi -

Your first where clause references ris.entity_level. Since that column is not part of the index, the cheapest method is to execute the full table scan on the table, which in the optimum case would be a single I/O. If you scanned the index first, the optimum case would require two I/O's: one for the index, then one for the records. Since the uniqueness of the index is so low (12 for 200,000 records), you would probably still be scanning large parts of the table in order to satisfy the "ris.entity_level = 1" clause.

A "FAST FULL SCAN" is an index scan. In this case (the modified where without the entity_level), the entire query (both the select and the where clause) reference columns that can be found completely within the index; therefore an index scan will satisfy the query without needing to reference the records. The fact that a scan is still done is due to the fact that the index is not unique.

Tim