Functional indexes ignored in sql plan (11.2.0.4 - SIMILAR)

Sep 30, 2014 3:31AM

Hi,

We had a 11.2.0.3 (unpatched).

compatible=11.2.0.3

cursor_sharing=SIMILAR

The following query (from T24 banking application):

SELECT t.RECID FROM FBNK_CUSTOMER t WHERE NVL(NUMCAST(EXTRACTVALUE(t.XMLRECORD,'/row/c23')),0)=3003;

Which was using the corresponding functional index based on: NVL(NUMCAST(EXTRACTVALUE(t.XMLRECORD,'/row/c23')),0)

With exec time in the hundredth of second.

We migrated to 11.2.0.4.2.

compatible=11.2.0.4 (but with others database with compatible=11.2.0.3, we have the same problem)

From now, the same query, just full scans the table.

Exec time is 50s.

Workaround:

cursor_sharing=EXACT

Functionnal index is used.

Exec time back to

SQL Performance (MOSC)