Re: Efficient delete of millions of rows from SQL

"Kevin Pullen" <kevin.pullen_at_onet.co.uk> schrieb im Newsbeitrag news:b5aba1dd.0109250616.31c1d4f6_at_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.

What about moving the rows you want to keep to a temporary table, then truncate oder drop the original table and then write back the rows from your temptable.

TH Received on Tue Sep 25 2001 - 09:33:15 CDT