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 :

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

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