Convert PLSQL update with forall

From: amonte <ax.mount_at_gmail.com>
Date: Sun, 20 May 2018 17:42:16 +0200
Message-ID: <CABV7K9us=f7kkRwrf6taoHFKcOuQpaUqG9k19w4hf-d4BV9PTA_at_mail.gmail.com>



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
Received on Sun May 20 2018 - 17:42:16 CEST

Original text of this message