Update by ROWID and using bulk collection

From time to time, DBA team is asked to do "scrubs" against our on line database. Sometimes, these scrubs can be very large updates. Usually the database is required to be online and accessible for users all the time. From my observation, our development team seems liking update-one-row-at-a-time approach very much. In their code handed over to us, typically they build driver tables based on some ids (most likely primary keys) and then update the target tables looping through the driver tables and commit every certain number of rows. From my recent research,there are probably two approaches that worth considering in order to improve the performance of large scrubs.

1. Update/delete based on ROWID.
Rowid access is the most efficient access path in Oracle. Here is a nice post at ITPUB (in Chinese) demonstrating this concept with a live production example.

2. Bulk collection feature

Below is a simple code example for using ROWID based driver table and bulk collection:

declare
   cursor c is
     select rowid id from t where val=0 order by rowid;
   counter number :=0;

   rowids dbms_sql.urowid_table;

begin
   open c;
   loop
      fetch c bulk collect into rowids limit 1000;
      forall i in 1..rowids.count
         update t set val=1
              where rowid = rowids(i);
      commit;
      counter := counter + 1;
      dbms_output.put_line('Updated batch '  counter   );
      exit when c%notfound;
   end loop;
   close c;
end;
/