Re: howto limit numbers delete command

From: Stephan Bressler <stephan.bressler_at_siemens.com>
Date: Wed, 2 Apr 2003 10:01:47 +0200
Message-ID: <b6e5ae$fo$1@news.mch.sbs.de>

Hi,

a solution would be to run
delete from <table>
where <your criteria here>
and rownum < MAX_DEL_PER_COMMIT

with MAX_DEL_PER_COMMIT set to 100.000 (or larger if that fits into your RBS). Run this statement in loops with a commit at the end of each loop until no rows are to be deleted.

BTW: Choose a large rbs segment at the beginning of the loop.

Cons:
- usually you have to scan the table multiple times.

Stephan

"Christian Förster" <cfoerst_at_web.de> wrote in message news:f5116a21.0304012346.4b751261_at_posting.google.com...
> Hi,
> I've got a question, which I can't google out ;-)
> We want to delete a large number of rows from a table.
> Cause this number is probably too large for our transaction-log,
> we have to do it step by step.
> In sybase this can be done by set rowcount.
> In mysql you can do it with the limit command.
> But how is that done in oracle pl/sql?
> I tried it with dynamic sql but to delete within a cursor takes hours
> of time (too much hours).
> Can you give me any suggestion for that problem ?
>
> Cheers
>
> Christian
Received on Wed Apr 02 2003 - 02:01:47 CST