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 -> Efficient delete of millions of rows from SQL

Efficient delete of millions of rows from SQL

From: Kevin Pullen <kevin.pullen_at_onet.co.uk>
Date: 25 Sep 2001 07:16:05 -0700
Message-ID: <b5aba1dd.0109250616.31c1d4f6@posting.google.com>


I need to delete several million rows. I cannot truncate the table, and any method of moving, viewing, partitioning etc will probably cost just as much time. I am looking for help on an idea that I used on a VAX. I was able to mark many many records for delete - but hold off on the Erase part of the function, which really improved performance. The rows were effectively flagged as removed but still occupied the physical disk space. The space was then recovered during the re-org - a time consuming job that is already scheduled and has to be run anyway.

This kind of theory - if it can be applied in any way to an Oracle environment (by the way, the delete is being issued from an SQR) will speed up the marking of the target rows. I have read of suggestions that use the idea of flagging rows by the use of a new column and then attending to the delete at a later date, but in my scenario this would not save me any time unless the re-org can drop or exclude these flagged rows.

Can anyone offer any suggestions please. Received on Tue Sep 25 2001 - 09:16:05 CDT

Original text of this message

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