| Subject | Re: [Firebird-Architect] the insert into t select * from t infinite |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2005-07-05T16:46:33Z |
Ann W. Harrison wrote:
delete from t t1
where t1.id in (select first 5 t t2
from t order by t2.id);
As it happens, that query can delete all of table t, and will if the id
field happens to be a value created by a generator, or any other
monotonically increasing value. Assuming an index on t.id, the engine
starts reading the index and finds that the first five entries (1-5) are
all valid. One of those numbers 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 is
deleted, so the engine skips it, and selects the next five (2-6). It
deletes that record. The next time through, the first two records in
the index are deleted, so the engine selects numbers 3-7. You get the
same effect, at much higher cost, if there is no index.
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.
Regards,
Ann
>Here's a particularly nasty query (at least I think it is)
> OK, not ignore them completely - the records are stored in the database
> in the completely normal way. The insert case is simple - the
> subselect that drives an insert ignores records that appear on the undo
> list of the insert. Update and delete are slightly more complicated.
> For those verbs, the subselect recognized that it must use the next
> older version of records that appear on the undo list for the update or
> delete.
>
delete from t t1
where t1.id in (select first 5 t t2
from t order by t2.id);
As it happens, that query can delete all of table t, and will if the id
field happens to be a value created by a generator, or any other
monotonically increasing value. Assuming an index on t.id, the engine
starts reading the index and finds that the first five entries (1-5) are
all valid. One of those numbers 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 is
deleted, so the engine skips it, and selects the next five (2-6). It
deletes that record. The next time through, the first two records in
the index are deleted, so the engine selects numbers 3-7. You get the
same effect, at much higher cost, if there is no index.
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.
Regards,
Ann