GilaMonster (10/31/2012) Andrew Diniz (10/31/2012)
After all, it is possible to observe 'Halloween Protection' in DELETE and INSERT plans too
No, the Halloween problem is exclusively for updates. You can't insert or delete a row multiple times, you can an update and that's the problem. You may see spools in insert or delete plans, but they are not there for halloween protection.
I beg to differ.
Consider a DELETE statement of the form:
WHERE COL < CONST
as opposed to
WHERE COL < (SELECT AVG(COL) FROM TABLE_NAME)
You'll see a spool in the second DELETE, and that spool is essential to protecting against deleting more rows than expected due to the fact that the AVG changes as a result of rows already deleted.
Basically, the DELETE condition depends on data in the table, and the condition changes as rows are deleted.
Conceptually this is exactly the same as Halloween Protection for UPDATES.
Consider another example:
WHERE COL1 NOT IN (SELECT COL2 FROM TABLE_NAME)
By deleting the row where COL1=2, we now have COL1=2 also unexpectedly matching the filter.
Similarly deleting COL1=2 could result in COL1=3 being deleted.
Without Halloween Protection, the DELETE statement empties the table.
So although Halloween Protection typically applies to UPDATE, it can apply to DELETE; and similar cases can be found for INSERT.