Hey Jurij, that certainly is elegant. I've seen several solutions to this problem and yours must have the fewest key-strokes.
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. 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.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Reply With Quote