Below is the output of execution plan for a update query , based on execution plan i need to figure
1. Looking at the explain pñan what are key factors that tell me which is degrading the performance.
2. How should we imporve to get good results.
3. Reweite the query
There are two tables cust,dept and i am updating cust table columns with some values using below update statement.
UPDATE cust
SET P1 = B1 ,
EDT_DATE = SYSDATE,
ID6= 999
WHERE P1 = B3 AND
((ID1 = B2 ) OR (ID1 IN (SELECT ID2 FROM DEPT WHERE ID3 =B2 )))
AND STAT IN (1, 4);
There are already indexes created on this table -
CUST
cust_IDX1(P1, C1, STAT)
DEPT
DEPT_IDX1(ID2)
DEPT_IDX2(ID3)
Plan hash value: 9999999999
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("ID1"=:B2 OR IS NOT NULL))
4 - access("P1"=:B3)
filter(("STAT"=1 OR "STAT"=4))
5 - filter("ID3"=:B2)
6 - access("ID2"=:B1)
Thanks