CODEWITHSUNDEEP
sqldatabaseoracle-database
skynova2070
skynova2070

Reputation: 29

Update table efficiently in Oracle

I have 3 tables A(a1, a2) , B (b1, b2), C (c1, c2). I want to perform update in the following way:

UPDATE A 
SET A.a2 =   (SELECT 2* SUM(C.c2) 
             FROM B JOIN C 
             ON B.b1 = C.c1 WHERE A.a1 = B.b2) 
WHERE A.a1 = (SELECT B.b2 
             FROM B JOIN C 
             ON B.b1 = C.c1 HAVING SUM(C.c2) > 1000);

The problem with this approach is the tables B and C need to be joined multiple times.

Is there any good solution to solve this problem?

I tried:

UPDATE A SET A.a2 = (SELECT CASE 
                            WHEN SUM(C.c2) > 1000 THEN 2*SUM(C.c2) 
                            ELSE A.a2 
                            END
                     FROM B JOIN C 
                     ON B.b1 = C.c1 
                     WHERE A.a1 = B.b2);

but it does not use the index on a1 and also it will update all rows in table A which is even slower than the original one

Upvotes: 0

Views: 51

Answers (3)

arpit
arpit

Reputation: 11

You can use "with clause"

update A
   SET A.a2 =
       ((with data as (SELECT B.b2, C.c2 FROM B JOIN C ON B.b1 = C.c1)
         select *
           from data_b_c)
         select * from data b_c, A where A.a1 = b_c.b2 HAVING SUM(b_c.c2) > 1000)

Upvotes: 0

user330315
user330315

Reputation:

This kind of update is typically faster when done with a MERGE instead:

MERGE INTO a
using
(
   SELECT b.b2, 2 * SUM(C.c2)  as c2_sum
   FROM B 
     JOIN C ON B.b1 = C.c1 
   group by b.b2
   HAVING SUM(C.c2) > 1000
) t on (t.b2 = a.a1)
when matched then update 
     set a2 = t.c2_sum;

Upvotes: 1

Michael Piankov
Michael Piankov

Reputation: 1997

If its really long operation you may create intermediate table where you calculate result. But you need to have primary key on both tables

 Create table int_a as 
 SELECT 2* SUM(C.c2) as new_a2
       , B.b2 
         FROM B JOIN C 
         ON B.b1 = C.c1 WHERE A.a1 = B.b2
         GROUP BY B.b2 
         HAVING SUM(C.c2) > 1000

 alter table int_a add constraint primary key pk_int_a (b2);
 alter table a add constraint primary key pk_a (a2);

and update table a with join 

 update (select a2, int_a.new_a2
           from  A, INT_A
          where A.a1 = INT_A.b2)
    set a2 = new_a2

Upvotes: 0

Related Questions