Hello Dominic
I understand a single sql statement can be quicker but there is no way. I
have simplify the code but it is more complex than that, more logics
involved that is why I am looking FORALL optimization. But are you saying
running 2 million updates is going to be slower than 4000 uodate
(considering arraysize of 500)?
Thank you
2018-05-20 19:16 GMT+02:00 Dominic Brooks <dombrooks@xxxxxxxxxxx>:
In principle, there is no reason why a FORALL would make this quicker.
Fetching data from sql into plsql to then drive other sql should be avoided.
Best approach *should* be a single MERGE statement, no loops.
But there are always caveats and exceptions.
Cheers
Dominic
Sent from my iPhone
On 20 May 2018, at 17:37, amonte <ax.mount@xxxxxxxxx> wrote:update statements using the cursor output, it looks like
Hello
I have a plsql procedure which contains a complex cursor and a couple of
aproximately 2 million rows and the process is not as fast as desired. I
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
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
