Deleting in a cursor loop

From: k_c <kcjunkmail_at_austin.rr.com>
Date: 28 Sep 2006 20:49:18 -0700
Message-ID: <1159501757.987321.165300@m7g2000cwm.googlegroups.com>

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. Received on Thu Sep 28 2006 - 22:49:18 CDT