Friday, November 6, 2009

Update massive number of records

Simple Pl/SQL procedure which help to do massive update.


create or replace PROCEDURE move_column AS
  type num_table IS TABLE OF NUMBER INDEX BY pls_integer;
  round_trips number;
  recid_table num_table;
  avid_table num_table;
  scale NUMBER;
  starting_point NUMBER;
  ending_point NUMBER;
BEGIN
  SELECT recid,
    attrib_value_id bulk collect
  INTO recid_table,
    avid_table
  FROM very_big_table;
  scale := SQL % rowcount;

  round_trips := TRUNC((scale + 9999) / 10000);


  FOR bite IN 1 .. round_trips
  LOOP
    starting_point :=(bite -1) *10000 + 1;
    ending_point := least(scale,   bite *10000);
    forall this IN starting_point .. ending_point

      UPDATE another_very_big_table SET rec_id = recid_table(this)
       WHERE id = avid_table(this);
    COMMIT;
    --DBMS_OUTPUT.PUT_LINE('Move: ' || ending_point);
  END LOOP;

END move_column;

No comments:

Post a Comment