DBA Tips Archive for Oracle

  


How to perform long deletes - (commiting every n number of records)

by Jeff Hunter, Sr. Database Administrator

When performing a DELETE from a table, Oracle will generate rollback. If the particular table you are trying to delete from contains a large number of records, it is possible for the transaction to fail because of the rollback segment not being large enough. One way to solve this issue is to use the TRUNCATE command. TRUNCATE does not generate rollback and has the added benefit of rebuilding any indexes on the table. Another solution would be to use the anonymous PL/SQL block below.

     DECLARE

       count  NUMBER  := 0;
       total  NUMBER  := 0;

       CURSOR del_record_cur IS
         SELECT rowid
         FROM   <OWNER>.<TABLE_NAME>
         WHERE  <YOUR_CRITERIA>

     BEGIN
       FOR rec IN del_record_cur LOOP
         DELETE FROM <OWNER>.<TABLE_NAME>
           WHERE rowid = rec.rowid;

         total := total + 1;
         count := count + 1;

         IF (count >= 1000) THEN
           COMMIT;
           count := 0;
         END IF;

       END LOOP;
       COMMIT;
       DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from <OWNER>.<TABLE_NAME>.');
     END;
     /

Update

Soon after publishing this months DBA Tip, Geurts Maarten wrote in to indicate that the above PL/SQL block may not work where you may have either many processes running on the system or if the table is too large. If you do not have a rollback segment large enough, you may encounter the "snapshot too old" error. This is because the code block is modifing the snapshot the first cursor uses to get its rowids from.

You need to reopen the snapshot.

A PL/SQL BLock like:

    BEGIN
    loop 
    Delete <OWNER>.<TABLE_NAME> where  <YOUR_CRITERIA> and rownum < 5000
    exit when SQL%rowcount < 4999
    end loop;
    END
will do the trick just fine.

Basically a commit in a loop will fail after some time if a cursor stays open all that time, especially if the data in the cursor is modified.

About the above PL/SQLblock:

It keeps looping (executing the delete statement) until it deletes less than 4999 rows. So this is the last delete. if you do not add additional where statements the whole table is deleted.

Also note i use rownum < 5000. the following

    BEGIN
    loop -- keep looping 
      --do the delete 4999 in each iteration
      Delete <OWNER>.<TABLE_NAME> where  <YOUR_CRITERIA> and rownum < 5000;
      -- exit the loop when there where no more 5000 reccods to delete. 
      exit when SQL%rowcount < 4999;
      -- commit to clear the rollback segments. 
      commit; -- this commit i forgot in the last mail, oops. 
    end loop;
    commit; -- commit the last delete
    END;
Note that the souce I typed is untested, it may have syntax errors. The point is: when you need big rollback statements it is getting slower. I have seen cases in a simular PL/SQL block (with an update instead of delete) where the first records were updated twice as fast as the last records. On the other hand, my constuction has to execute the SQL much more times, that is why i increased the commit size from 1000 to 5000.


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Monday, 23-Jan-2006 09:04:31 EST
Page Count: 72668