Avoiding Rollback Generation in Bulk Delete!!

Author: Steve Keating

Being Oracle database administrator the job of performing bulk deletes must not be new for you. Many times it happens that we have to schedule a job that deletes many records in the database. However the problem we face in such cases is that many rollback segments are generated as a result of this job. Therefore it is the responsibility of database administrator to use some efficient approach for avoiding the generation of rollback segments in bulk delete.

In this article we will discuss some great tips that will help you reduce the generation of rollback segments in bulk deletes. (Article Continues below...)

Using TRUNCATE Statement:

One simple solution to solve this problem is to use TRUNCATE statement instead of DELETE statement. The benefit of using TRUNCATE statement is that all your records in the table will be deleted without generation of any rollback segment. However you can not apply this solution in all scenarios. For example you can not use TRUNCATE command to delete partial records from the table.

Using DELETE Command in Iterations:

Another possible solution to this problem is to run the DELETE commands iteratively so as to minimize the generation of rollback segments. Below code can be used to clean up the rollback segments after every deletion of 1500 table records. Once the rollback segments are cleaned up then they can be reused again and again thus reducing the overhead of generating so many rollback segments as a result of bulk delete.

DECLARE
MYDELCOUNT NUMBER;

     BEGIN

          LOOP

              DELETE
              FROM MYTABLE
              WHERE
             (
                    ROWNUM<1501'
                    AND
                    MYOTHERCONDITION
              );
              
              SELECT COUNT(ROWID)
              FROM MYTABLE
              INTO MYDELCOUNT;
                  
              COMMIT;
              EXIT WHEN MYDELCOUNT = 0;
          END LOOP ;

     END; Read Again!!

 More Database Articles
   Database Security: Step by step guideline
   Improve performance by Optimizing SQL!!
   Amazing Tips to Fix Broken Oracle ASM Instance!!
   Some Facts about Oracle Business Strength
   Great Tips on Reusing Space after deletion of database data!!