Re: Deleting in a cursor loop

"k_c" <kcjunkmail_at_austin.rr.com> a écrit dans le message de news: 1159501757.987321.165300_at_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.
|

It's time to read the Concept manual.

1/ For your answer have a look at "Read consistency" section: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/intro.htm#sthref133

2/ For your very bad design search on Google, AskTom... for "snapshot too old', "ORA-1555", "ORA-01555" or "fetch across commit"

Regards
Michel Cadot Received on Fri Sep 29 2006 - 00:11:58 CDT