Thursday, February 7, 2013

Commit Strategy for Large Transactions

Let's face it.  Every DBA has come across a need to update a LOT of records.  The problem usually is that a mass update can take forever, blow out your redo logs, lock out other access, etc.  Here is the solution.  This was written and used in Oracle, but I'm thinking could easily be modified to work with any ANSI compliant relational database.

DECLARE

CURSOR cur IS
   select * from owner.table_temp;
                 
ct NUMBER(5) := 0;
group_size NUMBER(5) := 5000;

BEGIN
    FOR srmvt in cur LOOP
        INSERT INTO owner.table   (<columns>)
            VALUES  (srmvt.<columns>);
       ct := ct + 1;
       IF ct >= group_size THEN
           commit;
           ct := 0;
       END IF;
   END LOOP;
   commit;
END;

I have also used this for updates as well.

DECLARE
CURSOR cur2 IS  
   select * from yourcompany.yourtable;
                
ct2 NUMBER(5) := 0;
group_size NUMBER(5) := 5000;
BEGIN
   FOR srmvt2 in cur2 LOOP
        update yourcompany.yourtable set process_flag = 'SUCCESS'
        where transactionid = srmvt2.transactionid;
       ct2 := ct2 + 1;
       IF ct2 >= group_size THEN
           commit;
           ct2 := 0;
       END IF;
   END LOOP;
   commit;
END;
/

No comments:

Post a Comment