Re: need to delete 37000000 rows

On Sat, 27 Oct 2001 10:37:41 -0500, neuge <neuge_at_lconn.com> a écrit:
>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.

You can make a counter in your loop, then commit only when you reach a number of deleted rows in accordance with your rollback segment. (do not forget to reset your counter to 0 after commit, or to increase it after each delete :-) )

If you do this on a one-time basis, it could also be interesting to disable your indexes on this table, to rebuild them afterwards.

-- 
Damien