Hi,
i've a query written by a developer that i cannot modify.
This is the where condition:
WHERE ( UPPER(TRIM (CODFSC)) = UPPER (TRIM ( '01923980500'))
OR UPPER(TRIM (CODUIC)) = UPPER (TRIM ( '01923980500')))
There is an index on CODFSC and 1 on CODUIC.
the plan is:
Plan
SELECT STATEMENT ALL_ROWS Cost: 9,194 Bytes: 3,206,502 Cardinality: 15,054
1 TABLE ACCESS FULL TABLE ANAGRAFICA Cost: 9,194 Bytes: 3,206,502 Cardinality: 15,054
So i've created two new index on UPPER(TRIM (CODFSC)) and UPPER(TRIM (CODUIC)) but the plan
stil full scan.
Modifing the where condition in:
WHERE ( CODFSC = UPPER (TRIM ( '01923980500'))
OR CODUIC = UPPER (TRIM ( '01923980500')))
the plan is:
SELECT STATEMENT ALL_ROWSCost: 157 Bytes: 426 Cardinality: 2
5 CONCATENATION
2 TABLE ACCESS BY INDEX ROWID TABLE ANAGRAFICA Cost: 5 Bytes: 213 Cardinality: 1
1 INDEX RANGE SCAN INDEX ANAGRAFICA_IDX01 Cost: 3 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID TABLE ANAGRAFICA Cost: 152 Bytes: 213 Cardinality: 1
3 INDEX SKIP SCAN INDEX ANAGRAFICA_IDX02 Cost: 151 Cardinality: 1
Why optimizer do not use my funct index?
Thank you.