Re: need to delete 37000000 rows

One way that we've dealt with this kind of problem, though not at this magnitute is by running a SQL script like the following:

set transaction read only;
spool kill.sql;

select 'delete from tblXX where PK = ' || PK || ';' from tblXX where rows_I_want_to_delete = 1;

spool off;

You may also be able to use 'where ROWID = ' that may be better performing by not visiting the PK index (haven't tried this though)

We then have a shell script that inserts commit statements every 500 records or so (the records we are deleting are 14K on average, so you may be able to up the commit number) and then runs kill.sql

Not the most elegant, but we've tried a couple of others and run into the rollback problem. Note, we can't move data via temp tables and such because of the real-time nature of our product.

Couple of other notes: If you can deal with slow queries during the delete time, disable or drop the indexes, and then recreate or rebuild when done.

In article <3bdad336$0$13463$39cecf19_at_nnrp1.twtelecom.net>, "neuge" <neuge_at_lconn.com> wrote:

> 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

-- 

--------------
--- Remove any 'd's from the email address if you need to email me
--------------