Re: Convert PLSQL update with forall
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
forall i in 1 .. v_rows.count
exit when crsr%notfound;
declare
crsr sys_refcursor;
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 = 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;
end loop;
end;
/
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;
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' ORr.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 - 21:48:54 CEST