Provided test case:
select first 1 i.ID, i.ID_TYPE
from IDENT$IDENTIFIERS i
left join IDENT$LIFE l on l.ID_IDENT = i.ID
where (i.IDENTIFIER_VALUE = ? or i.IDENTIFIER_CODE = ?) and (i.ID_TYPE = ? or ? = 0)
order by l.CURRENT_STATE nulls last, i.ID_TYPE desc;Actual plan:
PLAN SORT (JOIN (I NATURAL, L INDEX (IDENT$LIFE_IDENTIFIER)))
Expected plan:
PLAN SORT (JOIN (I INDEX (IDENT$IDENTIFIER_VALUE, IDENT$IDENTIFIER_CODE), L INDEX (IDENT$LIFE_IDENTIFIER)))
The plan turns into he expected one if the (i.ID_TYPE = ? or ? = 0) part is omitted or hinted to avoid index usage (i.ID_TYPE+0 = ?).
Provided test case:
Actual plan:
Expected plan:
The plan turns into he expected one if the
(i.ID_TYPE = ? or ? = 0)part is omitted or hinted to avoid index usage (i.ID_TYPE+0 = ?).