Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: need to delete 37000000 rows
How many rows do you need to leave in the table, if thats not as many maybe
copying the rows out into a temporary table, by using create table as select
* command then truncate the original table then move the rows that you want
to keep back in.
"neuge" <neuge_at_lconn.com> wrote in message
news:3bdad336$0$13463$39cecf19_at_nnrp1.twtelecom.net...
> 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 - 15:13:38 CDT