# Re: Convert PLSQL update with forall

Date: Sun, 20 May 2018 23:26:30 +0200

Message-ID: <CABV7K9vkDn8M_x5ZWympBGWhNZUkXaGoP-EX4BOYwOfUry3t+Q_at_mail.gmail.com>

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-lReceived on Sun May 20 2018 - 23:26:30 CEST