Hi,
in my 9.2.0.8 DB I've got query like this:
SELECT COUNT (agreementno) cnt
FROM followup
WHERE agreementno = :v001 AND actioncode = :v002 AND resultcode = :v003;
Plan
SELECT STATEMENT CHOOSECost: 11 Bytes: 18 Cardinality: 1
5 SORT AGGREGATE Bytes: 18 Cardinality: 1
4 VIEW index$_join$_001 Cost: 11 Bytes: 18 Cardinality: 1
3 HASH JOIN Bytes: 18 Cardinality: 1
1 INDEX RANGE SCAN NON-UNIQUE IDX_FOLLOWUP06 Cost: 13 Bytes: 18 Cardinality: 1
2 INDEX RANGE SCAN UNIQUE PK_FOLLOWUP Cost: 13 Bytes: 18 Cardinality: 1
I need to change join order of indexes, so the proble one would be PK_FOLLOWUP .
Of course the best plan is index range scan on pk but during to hight CF Oracle is combining 2 indexes .
Regards.
Greg