best way to delete large number of rows

[jmodic]

Originally posted by abhaysk
Select count(*) into variable from table where ur condition

variable:=Round((variable)/(max records u think u can delete))+1

For cnt 1 to variable loop

delete with ur condition and with rownum < (max records u think u can delete)+1.

Loop.

You've forgotten to stick a COMMIT inside your loop aftere each batch delete.

Besides, you can code this much more elegantly without the need for the initial SELECT if instead of FOR LOOP you simply use LOOP with EXIT:

LOOP
  DELETE with ur condition and
         with rownum < (max records u think u can delete);
  EXIT WHEN sql%ROWCOUNT = 0;
  COMMIT;
END LOOP;