need to delete 37000000 rows

I am trying to delete 37 million rows from a database table in a timely fashion and without causing a rollback segment or snapshot too old error.

I have made several attempts at this and was able to create a procedure loop that would select and delete row number 1. This was developed and used sucessfully on tables with a significantly less number of rows (2 million). I have a distinct index on the key fields where selects return in 1sec or less.

This procedure works even on this large table. The problem is that it deletes about 800 records per second and at that rate would take around 116 hours to complete.

I was wondering is anyone has experience at deleting very large numbers of rows who could offer any suggestions.

--mike Received on Sat Oct 27 2001 - 10:37:41 CDT