CODEWITHSUNDEEP
oracle-database
user1616243
user1616243

Reputation: 11

ORACLE UPDATE CORRELATED QUERY

I have an update query as below which is using correlated method. This is taking too long to run. Is there a better way to rewrite this. Appreciate your help in advance.

UPDATE DAC A
   SET (acc_obj_header,
        acc_sub_header,
        acc_desc_header
   ) = (SELECT B.acc_obj,
               B.acc_sub,
               B.acc_desc
          FROM DAC B
         WHERE B.country_code = A.country_code
           AND B.comp_no = A.comp_no
           AND B.bu_no = A.bu_no
           AND B.acc_level_of_detail < A.acc_level_of_detail
           AND (B.acc_obj || B.acc_sub) = (SELECT MAX(C.acc_obj || C.acc_sub)
                                             FROM DAC C
                                            WHERE C.country_code = A.country_code
                                              AND C.comp_no = A.comp_no
                                              AND C.bu_no         =A.bu_no
                                              AND (C.acc_obj || C.acc_sub) <= (A.acc_obj || A.acc_sub)
                                              AND C.acc_level_of_detail < A.acc_level_of_detail
                                          )
           AND rownum < 2
       )

Upvotes: 1

Views: 884

Answers (1)

Alexander Tokarev
Alexander Tokarev

Reputation: 1035

When I faced with long-working updates like it I rewrote it to MERGE statement. So if the query works fast by itself just rewrite to merge else consider about rewriting the query.

I hope you have an FBI (function-based index) on (b.acc_obj || b.acc_sub) - it could improve the performance slightly.

Upvotes: 1

Related Questions