function based index is not picking by optimizer
Hi Experts,
update query is running on database and it is taking time. It has index in id column which is primary.
I created a function based index on table. When I check the execution plan.
It is simple update query.
update xxxxx set Historyevent=:1 , HistoryEvent_Pd=:2 ,
Event_index=:3 where id=:4 and partitiondatetime=:5
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | UPDATE | xxxxxx | |
|* 2 | TABLE ACCESS BY INDEX ROWID| xxxxxxxx | 1 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0012963 | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("PARTITIONDATETIME",TIMESTAMP' 2018-02-16
00:00:00.000000')=:5)
3 - access("ID"=:4)
Plan shows filter nvl. Internally optimizer is changing the predicate information.