Friday, March 9, 2012

Doing commits after n number of rows


If a certain table has lets say a million rows and you want to perform updates on each of the row in the table  without overloading 'undo tablespace' then one option could be by doing the commits after every 'n' number of rows.
below is the procedure that takes 'n'  as a parameter . This will let oracle to perform commits after 'n' rows are updated


 CREATE OR REPLACE PROCEDURE commit_n
 (v_comit_counter in number)
IS
        counter  number :=1;
BEGIN


FOR i in (select * from TABLE) 


LOOP
       update TABLE
       set test = 2;
       counter := counter+1;
 IF
        mod(counter,v_comit_counter) = 0
 THEN
       commit;
 END IF;


END LOOP;
       commit;
END;     
/

Now lets do the commits after every 1000 records

SQL> execute commit_n(1000)

Once the above procedure is executed, we can verify the number of  commits performed by above script using the SQL below

select  s.*, n.name
from  v$mystat s left outer join v$statname n
on  s.statistic# = n.statistic#
where   n.name = 'user commits'









No comments:

Post a Comment