Re: Efficient delete of millions of rows from SQL

Hi Kevin,

My idea, given your constraints is along the same line as John's..

You could use a PL/SQL block to break the deletion into multiple transactions using
dynamic PL/SQL and loops and committing every N records..there's a good example of this in Kevin Loney's
DBA Handbook..unfortunately it's too large to produce here. He does indicate that the procedure in his book originates from Oracle support so that may be the people to talk to ..

Essentially your procedure takes the delete command and the number of records after which to commit as parameters.. So, if your delete statement is 'delete from sales where cust_id = 102' and you want to commit after every 1000 records, you:

execute DELETE_COMMIT_PROCEDURE('delete from sales where cust_id = 102' ,1000);

Since this is distributed by Oracle support I'd give them a shout instead of reinventing the wheel..

Hope this helps,

Steve

"Kevin Pullen" <kevin.pullen_at_onet.co.uk> wrote in message 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.
Received on Wed Sep 26 2001 - 07:34:06 CDT