Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: howto limit numbers delete command
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
![]() |
![]() |