Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Efficient delete of millions of rows from SQL
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