Re: HELP: How to DELETE millions of records (selectively)

From: Francisco Lameira <francisco.j.lameira_at_telecom.pt> - What's the fastest way of selectively delete 2 million records from a table containing 10 million records? <<

How about approaching the problem by:

1) rename the original table to old_xxx
2) re-create the original table sized as desired
3) insert into original_table_name select * from old_xxx where .......

   so that you only insert the two million rows you want to keep. If you do not

   have enough rollback to support inserting in one statement. You can break
  the insert into several inserts with a commit after each. 4) Now drop and rebuild the indexes (they point to old_xxx) 5) truncacte and the drop old_xxx

OR

1. Select off the two million rows you want to keep into sqlldr format spool file
2. truncate the table
3. run sqlldr to re-insert the two million rows

I would use method one if the space is available in Oracle and Method two if it is not. The only other option I know is to run a bunch of small deletes so that you do not run out of rollback segments and degrade your overall system performance.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Thu Jun 19 1997 - 00:00:00 CDT