Re: Convert PLSQL update with forall

Hi Vikas

That is OK way. I will test it this week.

Thanks a lot

2018-05-20 20:42 GMT+02:00 Vikas Kaushik <vikaskaushik.ymca_at_gmail.com>:

> Hi Alex,
>
> 1. As a first step, u do a bulk collect to fetch say 10000 rows(limit
> 10000)
> 2. You can use two forall statements and put the condition in where clause:
>
> **** First forall ****
> update t1
> set c3 = c2 + c4
> where c1 = indx.c1
> and c2 = indx.c2
> and indx.c10='NO';
>
> **** Second forall ****
> update t1
> set c5 = c7 + c8
> where c1 = indx.c1
> and c2 = indx.c2
> and nvl(indx.c10, 'XX') <>'NO';
>
> Herein you are running the driving query only once and then using the
> conditions on data fetched in collection, so it should be faster.
>
> Please let me know your thoughts.
>
>
> On Sun 20 May, 2018, 11:48 PM amonte, <ax.mount_at_gmail.com> wrote:
>
>> 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.
>>
>> Thank you
>>
>> 2018-05-20 19:16 GMT+02:00 Jacek Gębal <jgebal_at_gmail.com>:
>>
>>> 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