TIP #1: How to minimize rollback segments generation in bulk delete ?

One of my client has monthly job which deletes many records. This job generates many rollback segments. The main idea of this post is to find out options to minimize rollback generation in bulk delete.

There are 2 options :

- Replace delete statement with truncate. Truncate deletes all records in table and does not generate any rollback segment. This solution may not be applicable in all cases. Actually this option is not applicable to my client because code should not be changed (Company rule!!!!).Besides, monthly job only deletes table partially.

- Run delete statement iteratively. The following shows sample code :

nCount number;
delete from table? where rownum<1001' and condition?;
select count(ROWID) from table? INTO nCount;
commit; --- Cleans up rollback
EXIT WHEN nCount = 0;

With this option, rollback segments cleans up after each deletion of 1000 records.
Then, these rollback segments can be reused.

1 comment:

Unknown said...

Why does your commit when placed into a loop clean up rollback?
Surely the same amount of rollback is generated whether you commit once, ten times or 100 times?
Tom Kyte says, commit when you need to commit based on your transaction, no sooner.