Re: Convert PLSQL update with forall

Hello Jacek

1. How do you split the cursor output into two collections...?
2. Are you saying move the WHERE condition in the cursor to the update statement? I did think about trying it but it was going to be worse, it's like running the query twice which takes too much time.

> 2 ways.
> 1. Split the collection into two and do for all on each of them
> 2. Push the where clause into where clause of the updates.
> The difference is that in option 1 you don't do filtering of collection in
> SQL.
>
> On Sun, 20 May 2018, 16:43 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