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

Re: Efficient delete of millions of rows from SQL

From: JohnM <john_at_scroll.demon.co.uk>
Date: Tue, 25 Sep 2001 22:33:26 +0100
Message-ID: <270DtXAmgPs7EwA3@scroll.demon.co.uk>


In article <b5aba1dd.0109250616.31c1d4f6_at_posting.google.com>, Kevin Pullen <kevin.pullen_at_onet.co.uk> writes
>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.

We had a similar problem. We handled it with Pro*C* and array processing where we committed after N searches (50,000 say) in order not to blow up the rollback segments.

It can also be done in PL/SQL setting a cursor, a loop and then committing after every N searches.

-- 
JohnM            More Football Delights #16
     "I have to sit down with him and see where we stand" ARSENE WENGER
Received on Tue Sep 25 2001 - 16:33:26 CDT

Original text of this message

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