"Ann W. Harrison" <aharrison@...> wrote:

>
> delete from t t1
> where t1.id in (select first 5 t t2
> from t order by t2.id);
>
> <snip>
>
> If we applied the logic I described above (assuming it can be applied)
> here's what happens. Still assuming an index on t.id, the engine starts
> reading the index and finds that the first five entries are all valid,
> and that the first one matches the value of t.id from the outer loop so
> that record is deleted. It gets the next value of t.id and goes to the
> index to find the first 5 records. The first record appears, at first
> glance, to be deleted, but it is also on the undo list for the delete.
> The engine goes to the previous version of that record, finds that it is
> legitimate for the current transaction, and keeps it in the list of 5.
> Again, the non-indexed case has the same behavior at greater cost.

Correct.


Dmitry