RE: Convert PLSQL update with forall

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: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of amonte Sent: Sunday, May 20, 2018 11:42 AM
To: Oracle-L Group <oracle-l_at_freelists.org> Subject: Convert PLSQL update with forall  

Hello

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

begin  

for indx in (complex query)

loop  

if indx.c10 = 'NO' then

update t1

set c3 = c2 + c4

where c1 = indx.c1

and c2 = indx.c2;

else

update t1

set c5 = c7 + c8

where c1 = indx.c1

and c2 = indx.c2

end if;  

end loop;

end;

/  

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  

Alex  

--
http://www.freelists.org/webpage/oracle-l