optimizer is unable to use function based index
need to reduce time of a query using NVL. I've created the function and analyzed the stats but the query isn't picking up the new index.
process runs once every hour and we need to reduce the overall time from 10mins to as quick as possible.
This is the statement and its explain plan:
UPDATE QM_WK_MODULERESULTS
SET courseblock = moduledevyear||'1'
WHERE NVL(selectionstatus,'NA') NOT IN('NA','TR','ST')
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 196K| 12M| 1666 (2)| 00:00:20 |
| 1 | UPDATE | QM_WK_MODULERESULTS | | | | |
|* 2 | TABLE ACCESS FULL| QM_WK_MODULERESULTS | 196K| 12M| 1666 (2)| 00:00:20 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
UPDATE QM_WK_MODULERESULTS
SET courseblock = moduledevyear||'1'
WHERE NVL(selectionstatus,'NA') NOT IN('NA','TR','ST')
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 196K| 12M| 1666 (2)| 00:00:20 |
| 1 | UPDATE | QM_WK_MODULERESULTS | | | | |
|* 2 | TABLE ACCESS FULL| QM_WK_MODULERESULTS | 196K| 12M| 1666 (2)| 00:00:20 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
0