Search code examples
sqloracle-databaseperformancesql-updatesubquery

Why does an oracle update takes longer to execute despite costing less?

I have an oracle update query as below:

> UPDATE MAIN_TABLE T1
>     SET T1.A_VALUE=(SELECT A_VALUE FROM TEMP_TABLE WHERE A_VALUE = 'NYCM.GN2')
>     WHERE 
>     T1.V_ID IN =7841
>     AND T1.S_ID = 
>     (
>     SELECT T2.S_ID
>     FROM MAIN_TABLE T1, T2_DETAILS T2
>     WHERE T1.S_ID = T2.S_ID 
>     AND T1.A_VALUE = 'NYCM.GN2'
>     AND T1.V_ID = 7065
>     );

It's Explain plan is enter image description here

Without the sub query, the main query takes less time to execute. Below query takes around 10 seconds.

UPDATE MAIN_TABLE T1
SET T1.A_VALUE=(SELECT A_VALUE FROM TEMP_TABLE WHERE A_VALUE = 'NYCM.GN2')
WHERE 
T1.V_ID IN =7841
AND T1.S_ID = A_VALUE
Solution

  • 'cost' is an internal value generated by the optimizer when evaluating different access plans for a given query. You really can't compare 'cost' between different queries. And even at that, the 'cost' is the optimizer's estimate. There could be lots of reasons for a 'less cost' query to actually run longer than a 'higher cost' query .. bugs in the optimizer, resource contention, etc.