CODEWITHSUNDEEP
sqloracle-database
Random
Random

Reputation: 13

Update with sub query running slow

I am trying write a query to run an update on a table with around 1 million records, the query is expected to update around 10000 records. The query takes around 10 minutes to complete which is too slow for my requirement. below is the query 

update   ABC   t1 
 set amount_pct = (select (t1.amount / b.masteramt) * 100  
 from (select id, mstr_group, year, month, sum(amount) as masteramt  
 from ABC where id = 110 
 group by id, mstr_group ,year, month)  b  
 where b.id = t1.id and b.mstr_group  = t1.mstr_group  
 and b.year *100 + b.month = t1.year * 100 + t1.month AND b.masteramt != 0) 
 where t1.id = 110

I tried creating a materialized view and used that instead of the original table in the select query Query for materialized code 

 CREATE MATERIALIZED VIEW mv1 AS 
 select id, mstr_group, year, month, sum(amount) as masteramt  
 from ABC 
 group by id, mstr_group , year, month

and re wrote the above query as 

update   ABC   t1 set amount_pct =
 (select (t1.amount / b.masteramt) * 100  from 
 (select * from mv1)  b  
 where b.id = t1.id and b.mstr_group  = t1.mstr_group  
 and b.year *100 + b.month = t1.year * 100 + t1.month AND b.masteramt != 0) 
 where t1.id = 110

the query was faster than the original query but still took around 7 minutes to complete . Is there a way to modify this query to run any quicker ? I am using oracle 11g and the table ABC has indexes on id and mstr_grp

Upvotes: 1

Views: 640

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269913

I don't think you need the group by in the subquery. Something like this should work:

update ABC t1 
    set amount_pct = (select t1.amount / sum(b.masteramt) * 100  
                      from ABC b
                      where b.id = 110 and
                            b.id = t1.id and 
                            b.mstr_group  = t1.mstr_group and
                            b.year * 100 + b.month = t1.year * 100 + t1.month 
                     ) 
where t1.id = 110;

Then, you want an index on (id, mstr_group, year, month).

Upvotes: 1

Related Questions