By all the strange, shocking recommendations I have seen: this is
clearly the best, most obvious solution.
Put your select into the driving query of the merge and use the decode
solution mentioned in an other answer to write the update path.
The main query running an hour seems to be to long by an educated guess
(although one never knows, I have no evidence).
It should be cared for.
Am 20.05.2018 um 19:16 schrieb Dominic Brooks:
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:--
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
//www.freelists.org/webpage/oracle-l
