Delete large amount of data....

If you are at home and want to delete large data from tables then just go ahead and delete it. No problem at all.


But ! But, What to do when you have a task to delete some unused/bad data (Usually these number is large) from very critical large table.


Before executing well known delete sql statement you need to think twice(or may be thrice ...counting....). first you should ask yourself whether delete is require, if yes then you need to calcualte amount of data that you want to delete, and then comapre that amount with actual number of records of table. If amount of unused rows exceed 65%(Approx., Dont expect rule of thumb here) of total size of table then transfer good data into some other identical table,drop old one, create require index in new table....and its done.(Yes! In most cases its done!!)


You need to take lots of other thing in consideratin as well, before executing delete operation

Some are:
First identify what is off-peak hours for your business.
calcualte size of data that you need to delete.
estimat amount of redo that might generate.
estimat time that it would take to complet delete operation, rebuilding indexes etc..
Try to devide whole delete operation in small chunks.
Use paralle and nologgin operation (With care, obviously !).