Re: Deletion Of 160 Million Rows.

  • From: ryan_gaffuri@xxxxxxxxxxx
  • To: sheldonquinny@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 08 Feb 2005 14:00:44 +0000

how long this will take to run is far more proportionally to the number of 
bytes being deleted than to the number of records. Since the row is copied to 
the undo tablespace, so you are counting bytes.

even if you have a thing 2 column tables with numbers. You are looking at days 
minimum to do this. 

if this is a one time thing do the following:

1. create table as nologging with the records you want to keep. 
2. Create all indexes at the same time on the new table in nologging mode.
3. add constraints
4. drop the old table
5. rename new table to old table
6. rename constraints back to old names. 

do not do deletes. if this is going to be a regular batch process than your new 
table should be implemented with partitioning. Delete is way too costly. even 
with a thin table on most modern hardware this will run for days. 
-------------- Original message -------------- 

> Hi, 
> I Would Just LIke To Ask Whether It Is Possible To Delete 60 Million 
> Rows. At A Strech And Without Undo Contention. The Requirenment Is To 
> Delete 60 Million Records From The 160 Million Records. Its An OLTP 


Other related posts: