RE: Convert PLSQL update with forall

From: Ken Naim <>
Date: Mon, 21 May 2018 09:39:42 -0400
Message-ID: <011501d3f109$2cccdf20$86669d60$>

Another option is to load the 2 million records into a global temporary table, and then do a select on c10 where you can do a forall for c10=’NO’ and forall c10<>’NO’ assuming there are no nulls or nvl(c10,’~’)<>’NO’ if there are nulls. You can use a merge instead of the foralls as well with the same where clauses in the select on the global temporary table.  

From: <> On Behalf Of amonte Sent: Sunday, May 20, 2018 11:42 AM
To: Oracle-L Group <> Subject: Convert PLSQL update with forall  


I have a plsql procedure which contains a complex cursor and a couple of update statements using the cursor output, it looks like    


for indx in (complex query)


if indx.c10 = 'NO' then

update t1

set c3 = c2 + c4

where c1 = indx.c1

and c2 = indx.c2;


update t1

set c5 = c7 + c8

where c1 = indx.c1

and c2 = indx.c2

end if;  

end loop;



The cursor (join of 7 tabls and a few EXISTS subqueries) returns aproximately 2 million rows and the process is not as fast as desired. I was looking into FORALL to improve this procedure but I cannot find a way due to the conditional updates (two different update statements). Anyone's got an idea if FORALL can be implemented in such situations?  

Thank you very much  


Received on Mon May 21 2018 - 15:39:42 CEST

Original text of this message