Avoiding Rollback Generation in Bulk Delete

Avoiding Rollback Generation in Bulk Delete!!
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.
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 ;