Re: Convert PLSQL update with forall

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_at_gmail.com> 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
>>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 




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