Skip navigation.

How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?

Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.

To fix this problem one can easily rewrite code like this:

FOR records IN my_cursor LOOP
   ...do some stuff...
   COMMIT;
END LOOP;
COMMIT;

... to ...

FOR records IN my_cursor LOOP
   ...do some stuff...
   i := i+1;
   IF mod(i, 10000) = 0 THEN    -- Commit every 10000 records
      COMMIT;
   END IF;
END LOOP;
COMMIT;

If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback
segments.

NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.

one can also use objects as an alternative

create or replace type o_rec is object (dummy varchar2(1))
/
create or replace type c_rec is table of o_rec
/

declare
   c_rec_v c_rec;
begin
-- get our rows into a collection (or whatever)
   select cast(multiset(select * from (
         select *
         from dual
      )) as c_rec)
   into c_rec_v
   from dual
   ;
-- drive the loop from the collection
-- instead of from a cursor
   for i in 1..nvl(c_rec_v.count,0) loop
-- stuff happens
      commit;
   end loop;
end;
/

Here we use whole object assignment to select our rows into an object collection and then use the collection as the driver of the loop rather than a cursor. This avoids fetch across commit altogether. You could just as easily have used other array structures if you don't like objects, but you should like objects in oracle, its the next step to better more intelligent and capable pl/sql code.

Of course there are some caveats like with everything. You must remember that collections are basically memory structures and thus consume memory. There is a practical limit to how large they can get before they cause problems. And then there is the whole philosophical discussion of "does this change the basic nature of your transaction". But all in all it works pretty good for most cases.

Lastly one must point out, if you have to use tricks like this to get around problems like snapshot too old, then maybe the basic algorithm you are using to get the work done needs rethinking. But most people don't want to hear that.

of course, the best way may be using sql only

Consider this sequence of code that does no looping in plsql at all. In it we see the update join syntax as a means of doing what used to be written commonly as a plsql loop. 9i and 10g have new features we should try to exploit because they are much more efficient. If you get an undo error doing this then the reason is your undo is juts too small, not because there is some fetch across commits happening.

This sample code just shows one update. Of coures there can be any amount of sql going on here. Join Update, Merge, Oracle Analytics, ... you name it. Its like Tom Kyte always said, sql solution first, then pl/sql.

SQL> 
SQL> drop table t1
  2  /

Table dropped.

SQL> 
SQL> drop table t2
  2  /

Table dropped.

SQL> create table t1
  2  (
  3   a number not null
  4  ,b number not null
  5  )
  6  /

Table created.

SQL> 
SQL> create table t2
  2  (
  3   a number not null
  4  ,b number not null
  5  )
  6  /

Table created.

SQL> 
SQL> alter table t1 add primary key (a)
  2  /

Table altered.

SQL> alter table t2 add primary key (a)
  2  /

Table altered.

SQL> 
SQL> update (
  2          select t1.*
  3                ,t2.a na
  4                ,t2.b nb
  5          from t1
  6              ,t2
  7          where t1.a = t2.a
  8         )
  9     set b = nb
 10  /

0 rows updated.

SQL> 
SQL> insert into t1 values (1,1);

1 row created.

SQL> insert into t2 values (1,2);

1 row created.

SQL> 
SQL> select * from t1;

         A          B
---------- ----------
         1          1

SQL> select * from t2;

         A          B
---------- ----------
         1          2

SQL> update (
  2          select t1.*
  3                ,t2.a na
  4                ,t2.b nb
  5          from t1
  6              ,t2
  7          where t1.a = t2.a
  8         )
  9     set b = nb
 10  /

1 row updated.

SQL> 
SQL> select * from t1;

         A          B
---------- ----------
         1          2

SQL> select * from t2;

         A          B
---------- ----------
         1          2

SQL> 
SQL> alter table t2 drop primary key
  2  /

Table altered.

SQL> 
SQL> insert into t2 values (1,3);

1 row created.

SQL> 
SQL> select * from t1;

         A          B
---------- ----------
         1          2

SQL> select * from t2;

         A          B
---------- ----------
         1          2
         1          3

SQL> 
SQL> update (
  2          select t1.*
  3                ,t2.a na
  4                ,t2.b nb
  5          from t1
  6              ,t2
  7          where t1.a = t2.a
  8         )
  9     set b = nb
 10  /
   set b = nb
       *
ERROR at line 9:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> 

A confusing error with Join Update, till you understand what happened. Basically you must make sure the driving query brings back only one row as defined by the primary key of the table being updated. Otherwise you will attempt to update the same row twice which does not make sense.

And don't think you can fool Oracle into doing it just by supplying data that does not do multiple updates to the same row even though the key structures are not in place.

SQL> delete from t2 where b = 2;

1 row deleted.

SQL> select * from t1;

         A          B
---------- ----------
         1          2

SQL> select * from t2;

         A          B
---------- ----------
         1          3

SQL> update (
  2          select t1.*
  3                ,t2.a na
  4                ,t2.b nb
  5          from t1
  6              ,t2
  7          where t1.a = t2.a
  8         )
  9     set b = nb
 10  /
   set b = nb
       *
ERROR at line 9:
ORA-01779: cannot modify a column which maps to a non key-preserved table

We saw before that this data like this updated just fine. Problem is, there is no primary key on T2 now so Oracle knows there is potential for more than one version of rows to materialize for the table targeted for update.

Putting the PK back on T2 fixing things.

SQL> alter table t2 add primary key (a)
  2  /

Table altered.

SQL> 
SQL> update (
  2          select t1.*
  3                ,t2.a na
  4                ,t2.b nb
  5          from t1
  6              ,t2
  7          where t1.a = t2.a
  8         )
  9     set b = nb
 10  /

1 row updated.

SQL> select * from t1;

         A          B
---------- ----------
         1          3

SQL> select * from t2;

         A          B
---------- ----------
         1          3

SQL> 

Good luck, Kevin