Wednesday, 24 October 2018

Recursive update of all records in a table with huge data


Method 1:

Begin
 loop
   update xx_test_table
   set  xx_flag='Y'
 where xx_flag='N'
  and rownum <= 5000;
exit when sql%notfound;
commit;
end loop;
commit;
end;

Method 2:

declare
type rowid_array_tbl_type is table of rowid;
ids_tbl rowid_array_tbl_type;

cursor cur is
select rowid as id
from xx_test_table
where xx_flag='N';

begin
      open cur;
      loop
         fetch cur bulk collect into ids_tbl limit 1000;
         exit when ids_tbl.count = 0;
forall i in ids_tbl.first .. ids_tbl.last
            update xx_test_table set  xx_flag='Y' where rowid=ids_tbl(i);
commit;   
      end loop;

    end;

No comments:

Post a Comment