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@xxxxxxxxx> 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@xxxxxxxxx> 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
