Database Tuning (MOSC)

MOSC Banner

function based index is not picking by optimizer

edited Feb 13, 2019 4:02AM in Database Tuning (MOSC) 34 commentsAnswered ✓

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center