Re: Convert PLSQL update with forall

Hi Jacek

Appreciate all the examples! I will have a look them deeply sometime this week and see which is the most approriate way.

The update is indexed.

Thank you very much

2018-05-20 21:48 GMT+02:00 Jacek Gębal <jgebal_at_gmail.com>:

> There are actually several ways do to this, all depends on your server and
> data sizes.
>
> In general, maybe 2M updates with access by index aren't most efficient.
> Perhaps a full table scan would be better.
> FOR and FROALL will force index access, if index is there (and I hope
> there is).
>
> Anyway, I would consider the below options as possible solutions.
>
> declare
> type t_rows is table of complex_query%rowtype;
> c_limit constant positiven := 500000;
> v_rows t_rows;
> crsr sys_refcursor;
> begin
> open crsr for (complex_query);
> loop
> fetch crsr bulk collect into v_rows limit c_limit;
>
> forall i in 1 .. v_rows.count
> update t1
> set c3 = c2 + c4
> where c1 = v_rows(i).c1
> and c2 = v_rows(i).c2
> and indx(i).c10 = 'NO';
>
> forall i in 1 .. v_rows.count
> update t1
> set c5 = c7 + c8
> where c1 = v_rows(i).c1
> and c2 = v_rows(i).c2
> and indx(i).c10 != 'NO' OR indx(i).c10 is null;
>
> exit when crsr%notfound;
> end loop;
> end;
> /
>
> declare
> type t_rows is table of complex_query%rowtype;
> c_limit constant positiven := 500000;
> v_rows t_rows;
> v_rows_no t_rows;
> v_rows_yes t_rows;
>
> crsr sys_refcursor;
> procedure split_rows(
> p_rows t_rows,
> p_rows_no out nocopy t_rows,
> p_rows_yes out nocopy t_rows)
> is
> begin
> p_rows_no := t_rows();
> p_rows_yes := t_rows();
> for i in 1 .. p_rows.count loop
> if p_rows(i).c10 = 'NO' then
> p_rows_no.extend;
> p_rows_no(p_rows_no.last) := p_rows(i);
> else
> p_rows_yes.extend;
> p_rows_yes(p_rows_yes.last) := p_rows(i);
> end if;
> end loop;
> end;
> begin
> open crsr for (complex_query);
> loop
> fetch crsr bulk collect into v_rows limit c_limit;
> split_rows(v_rows, v_rows_no, v_rows_yes);
>
> update t1
> set c3 = c2 + c4
> where (c1,c2)
> in (select r.c1, r.c2 from table(v_rows_no) r);
>
> update t1
> set c5 = c7 + c8
> where (c1,c2)
> in (select r.c1, r.c2 from table(v_rows_yes));
>
> exit when crsr%notfound;
> end loop;
>
> end;
> /
>
> declare
> type t_rows is table of complex_query%rowtype;
> c_limit constant positiven := 500000;
> v_rows t_rows;
> crsr sys_refcursor;
> begin
> open crsr for (complex_query);
> loop
> fetch crsr bulk collect into v_rows limit c_limit;
>
> update t1
> set c3 = c2 + c4
> where (c1,c2)
> in (select r.c1, r.c2 from table(v_rows) r where r.c10 = 'NO');
>
> update t1
> set c5 = c7 + c8
> where (c1,c2)
> in (select r.c1, r.c2 from table(v_rows) r where r.c10 != 'NO' OR
> r.c10 is null);
>
> exit when crsr%notfound;
> end loop;
>
> end;
> /
>
> declare
> type t_rows is table of complex_query%rowtype;
> c_limit constant positiven := 500000;
> v_rows t_rows;
> crsr sys_refcursor;
> begin
> open crsr for (complex_query);
> loop
> fetch crsr bulk collect into v_rows limit c_limit;
>
> merge into t1
> using (select * from table(v_rows)) r
> on (t1.c1 = r.c1 and t1.c2 = r.c2)
> when matched then
> update set
> c3 = case when r1.c10 = 'NO' then c2 + c4 else c3 end,
> c5 = case when r1.c10 = 'NO' then c5 else c7 + c8 end
> ;
>
> exit when crsr%notfound;
> end loop;
>
> end;
> /
>
>
>
>
> On 20 May 2018 at 20:22, Pawel Kwiatkowski <yarel79_at_gmail.com> wrote:
>
>> Have you tried something like :
>>
>> begin
>> for indx in (complex query) loop
>> update t1 set
>> c3 = decode(c10,'NO',c2 + c4,c3),
>> c5 = decode(c10,'NO',c5, c7 + c8)
>> where
>> c1 = indx.c1
>> and c2 = indx.c2;
>> end loop;
>> end;
>> /
>>
>> br,
>> Pawel
>>
>> On Sun, May 20, 2018 at 5:42 PM, 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