Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: need to delete 37000000 rows

Re: need to delete 37000000 rows

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 27 Oct 2001 10:00:01 -0700
Message-ID: <9rep6h07sb@drn.newsguy.com>


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 Corp 
Received on Sat Oct 27 2001 - 12:00:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US