Frequent commits ?

[nithink]

Hi,
I've a delete statement inside my unix shell script, which will delete around 8 million rows.
Is there anyway that I can do frequent commits rather than doing commit after 8 million rows.
I've set the autocommit option ON and the statement is given below,

delete from table1 a where
exists (select '1' from table2 b where a.p_code = b.p_code);

Thanks much...

 

[carp]

The problem with AUTOCOMMIT is that it commits after the specified number of DML commands execute - so your script will still wait until the 8M rows are done.

If you want to commit after every 10000 rows, I think this would do it for you:

BEGIN
   LOOP
      delete from table1 a where
      exists (select '1' from table2 b 
              where a.p_code = .p_code)
      AND rownum < 10001;
      EXIT WHEN sql%ROWCOUNT = 0;
      COMMIT;
   END LOOP;
END;
/
[code]