Hi Tom:

I have a procedure that update a table with 200 millions of rows. Here is the original code:

PROCEDURE updt_tab

FOR rec IN (SELECT *
from TABLE1 V
left outer join TABLE2 S
on V.polnum = S.s7poln
AND V.polren = S.s7renn
AND V.polseq = S.s7seqn
AND V.vehnum = S.s7vehn
AND V.linecvg = S.s7lcvg) LOOP

update TABLE1 P
set P.CEDEDPOL = rec.s7ceding
where P.polnum = rec.polnum
and P.polren = rec.polren
and P.polseq = rec.polseq
and P.vehnum = rec.vehnum
and P.linecvg = rec.linecvg
and P.sat = 'ABC';

update TABLE1 P
set P.CEDEDCVG = rec.s7cedecvg
where P.polnum = rec.polnum
and P.polren = rec.polren
and P.polseq = rec.polseq
and P.vehnum = rec.vehnum
and P.linecvg = rec.linecvg
and P.sat = 'ABC';

END LOOP;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
NULL;

END UPDT_TAB;

Above procedure tables many hours to run, to make it run faster, I used bulk collect and forall and limit(so I don't run out of PGA memory) to rewrite the code:

PROCEDURE updt_tab

IS
CURSOR table1_cur
IS
SELECT *
from TABLE1 V
left outer join TABLE2 S
on V.polnum = S.s7poln
AND V.polren = S.s7renn
AND V.polseq = S.s7seqn
AND V.vehnum = S.s7vehn
AND V.linecvg = S.s7lcvg;

TYPE table1_collect is TABLE OF table1_cur%ROWTYPE
INDEX BY PLS_INTEGER;
table1_col table1_collect;

BEGIN
OPEN table1_cur
LOOP
FETCH table1_cur
BULK COLLECT INTO table1_col LIMIT 500;

FORALL indx IN 1 .. table1_col.COUNT
LOOP
update table1 P
set P.CEDEDPOL = rec.s7ceding
where P.polnum = rec.polnum
and P.polren = rec.polren
and P.polseq = rec.polseq
and P.vehnum = rec.vehnum
and P.linecvg = rec.linecvg
and P.sat = 'ABC';
update table1 P
set P.CEDEDCVG = rec.s7cedecvg
where P.polnum = rec.polnum
and P.polren = rec.polren
and P.polseq = rec.polseq
and P.vehnum = rec.vehnum
and P.linecvg = rec.linecvg
and P.sat = 'ABC';

END LOOP;
EXIT WHEN table1_col.COUNT < 500;

END LOOP;
CLOSE table1_cur;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;

END UPDT_TAB;

Do you think the rewritten code will perform much faster? I have yet to test it once our testing environment becomes available. Any issues that I might run into? Your expert insights is very much appreciated.

Thank you.

JS

PROCEDURE updt_tab
<b>/*</b>
FOR rec IN (SELECT *
                from TABLE1 V
                left outer join TABLE2 S
                on V.polnum = S.s7poln
                AND V.polren = S.s7renn
                AND V.polseq = S.s7seqn
                AND V.vehnum = S.s7vehn
                AND V.linecvg = S.s7lcvg) LOOP
                               
update TABLE1 P
      set P.CEDEDPOL = rec.s7ceding
      where P.polnum = rec.polnum
      and P.polren = rec.polren
      and P.polseq = rec.polseq
      and P.vehnum = rec.vehnum
      and P.linecvg = rec.linecvg
      and P.sat = 'ABC';

update TABLE1 P
      set P.CEDEDCVG = rec.s7cedecvg
      where P.polnum = rec.polnum
      and P.polren = rec.polren
      and P.polseq = rec.polseq
      and P.vehnum = rec.vehnum
      and P.linecvg = rec.linecvg
      and P.sat = 'ABC';
   
END LOOP;
COMMIT;<b>
*/  -- we don't need that code at all!!!!!
  NULL;</b>
  EXCEPTION
  WHEN OTHERS THEN
  NULL;

END UPDT_TAB; 

merge into table1 p
using (SELECT *
  from TABLE1 V
  left outer join TABLE2 S
  on V.polnum = S.s7poln
  AND V.polren = S.s7renn
  AND V.polseq = S.s7seqn
  AND V.vehnum = S.s7vehn
  AND V.linecvg = S.s7lcvg) rec
on (P.polnum = rec.polnum
      and P.polren = rec.polren
      and P.polseq = rec.polseq
      and P.vehnum = rec.vehnum
      and P.linecvg = rec.linecvg
      and P.sat = 'ABC')
when matched then update set P.CEDEDPOL = rec.s7ceding, P.CEDEDCVG = rec.s7cedecvg ;