Re: Efficient delete of millions of rows from SQL

How are you planning to do the 're-org' ? (I won't ask why).

If your plan is export/import, and your version of oracle is a recent one, then you can mark the rows as deleted by adding the extra column, then add a 'where clause' to the export to restrict the export to the unflagged rows.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Kevin Pullen wrote in message ...


>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.