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;
Friday, November 6, 2009
Update massive number of records
Simple Pl/SQL procedure which help to do massive update.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment