sql tuning

[Roshan]

Hello Erman,

could you please help me with the following sql script below?
Please find below the execution plan

sqltune.txt

I would like to know if writing the sql using merge would be ok.

Initial:
UPDATE DWH_TOT_BILL_AMT s

   SET s.cn =

          (  SELECT COUNT (*)

               FROM DWH_DETAIL_BILL_AMT c

              WHERE s.account_code_n = c.account_code

           GROUP BY c.account_code)

WHERE s.LAST_BILL_DATE IS NULL;

Tuned sql:

MERGE INTO DWH_TOT_BILL_AMT S

(SELECT COUNT(*) FROM DWH_DETAIL_BILL_AMT  GROUP BY account_code) c

ON (C.ACCOUNT_CODE=S.ACCOUNT_CODE)

WHEN MATCHED THEN UPDATE s

   SET s.cn = c.COUNT(*) WHERE s.LAST_BILL_DATE IS NULL;

Thanks,

Roshan

[ErmanArslansOracleBlog]

You re accessing DWH_DETAIL_BILL_AMT with Full Table Scan, and that's the reason of the high cost.
Tune your query accordingly.