Originally posted by DaPi
However (sorry!) I'm not sure it's the quickest. If it's only being done once, perhaps that's no big deal . . . it always depends! But since we are deleting 1mio records out of 2mio, I suspect you'll do a Full Table Scan (which will include reading the space occupied by the deleted data) most times you go through the loop.
Yes, I agree. If the delete is done based on the full table scan, then each loop iteration would perform a new FTS, rereading allready deleted data blocks. This surely wouldn't be the fastest possible sollution. If on the other hand te delete would be performed based on the index access, the performance would be the same as if you'd delete all rows in one go. But then again, it wouldn't be too clever to delete such a large portion of the rows based on the index range scan. BTW, this kind of the batch-delete inside a loop was not my suggestion, I merely "polished" abhaysk proposed code.

I would try PL/SQL, a cursor to select the rows to delete, set up a counter and IF MOD(counter, 50000) = 0 THEN COMMIT; END IF; not forgetting a COMMIT outside the loop. This should read each block only once.
Oh, don't try that! You'll get ORA-1555 sooner or later!