Re: Deleting in a cursor loop

k_c 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.
> 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? Or do I run the risk of
> actually picking up the new rows into the cursor thus perpetuating the
> runtime of the proc?
>
> thx in advance.

Follow Michel's advice but also cursor loops are ALMOST NEVER the right solution for any processing in Oracle.

You want to avoid single-row fetching and DML for which the solution is array processing with BULK COLLECT and FORALL. You can find the documentation on these at http://tahiti.oracle.com and working demos in Morgan's Library at www.psoug.org.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group