Re: Deleting in a cursor loop

"k_c" <kcjunkmail_at_austin.rr.com> wrote:
> I'm relatively new to PL/SQL. I need to delete 15million rows from a
> 75million row table. I've created a stored proc to do this using a
> cursor for loop and an iterative commit within the loop every 300,000
> rows deleted.

Why the intermediate commits?

> This is the proc:
>
> CURSOR delrowcur IS
> SELECT rowid FROM TABLE where dotype = 'X';
> delrowcur_rec delrowcur%ROWTYPE;
> BEGIN
> RCOUNT :=0;
> DCOUNT :=0;
> FOR delrowcur_rec IN delrowcur LOOP
> DELETE FROM TABLE WHERE rowid = delrowcur_rec.rowid;
> RCOUNT := RCOUNT + 1;
> DCOUNT := DCOUNT + 1;
> IF (RCOUNT >= 300000) THEN
> COMMIT;
> RCOUNT :=0;
> DBMS_OUTPUT.PUT_LINE('COMMITTING AT ROW: '|| DCOUNT);
> END IF;
> END LOOP;
> COMMIT;
> END;
>
> My question is this: I have a process which is continually inserting
> new rows into the table I'm purging from. If new rows are inserted
> into this table while also purging from it with this proc, will the
> proc ignore the new rows because the cursor has already fetched the
> finite set of rows the loop will delete from?

Kind of. But what happens if the rowid gets re-used in the mean time, or it got changed so that dotype = 'X' is no longer true?

> Or do I run the risk of
> actually picking up the new rows into the cursor thus perpetuating the
> runtime of the proc?

Not only that, but what happens when you need to restart your stored proc because of a server crash or the almost inevitable ORA-1555 error which happened somewhere in the middle?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB