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