Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: need to delete 37000000 rows
In article <3bdad336$0$13463$39cecf19_at_nnrp1.twtelecom.net>, "neuge" says...
>
>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
>
>
How many rows would be left in the table? would it be more effcient to
create table TEMP unrecoverable as
select <the rows you want to KEEP -- an "anti" delete, do it in parallel if you
want>;
truncate table old_table;
insert /*+ APPEND */ into old_table select * from TEMP;
(or instead of inserting, index the TEMP table using parallel unrecoverable indexing, drop the old table and rename temp to old_table).
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Sat Oct 27 2001 - 12:00:01 CDT