Re: Convert PLSQL update with forall

From: Jacek Gębal <jgebal_at_gmail.com>
Date: Sun, 20 May 2018 20:48:54 +0100
Message-ID: <CACQ9E3vaVDY7kTCbnRmV9BfycjN44yWnAtMs00CbsFDqNLxEjQ_at_mail.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
Received on Sun May 20 2018 - 21:48:54 CEST

Original text of this message