| Subject | Re: [firebird-support] min() and max() performance |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2005-02-10T15:54:58Z |
jonesjeffreyr wrote:
Firebird indexes contain entries that are not relevant to all
transactions. Index entries may represent record versions that are too
new or too old for the current transaction: too new because they're not
yet committed, too old because the record version they represent has
been changed or deleted - either by the current transaction or by
another transaction that's already committed.
What this means is that although you can easily get to the last entry in
an index, you may have to back up to get to the last entry that is
appropriate for your transaction. Within a single index pages, that's
awkward because of compression. Moving backward across index pages is
not reliable at all.
Firebird indexes are volatile. Index pages split when they get too full
and recombine when they get too empty. The splits and recombinations
don't lock the index - they never lock more than one page at a time.
Therefore, while you're walking an index, it can be changing in front of
you and in back of you.
Firebird's design requires that on-disk structures not cause deadlocks
when they are modified. A thread can hold only one page locked for
write at a time, and must generally be able to complete its changes and
release the page without acquiring a lock on another page. The
exception called a "hand off" and allows a threads to release one page
and get another if the pages follow a specific order. In the case of
indexes, that order is left to right - low to high.
As a result, the low to high path across an index will always be
correct, but the high to low path has to be fixed up later, and may, at
times, not be current - the back point may indicate a page that's no
longer part of the index - or even a page that's been released from one
part of the index and used in another.
Regards
Ann
>Several factors working together.
> can some one explain why you need am index sorted in descending order
> for the max() function go create a plan that uses the index. the
> same holds true for the min() function and ascending indexes.
Firebird indexes contain entries that are not relevant to all
transactions. Index entries may represent record versions that are too
new or too old for the current transaction: too new because they're not
yet committed, too old because the record version they represent has
been changed or deleted - either by the current transaction or by
another transaction that's already committed.
What this means is that although you can easily get to the last entry in
an index, you may have to back up to get to the last entry that is
appropriate for your transaction. Within a single index pages, that's
awkward because of compression. Moving backward across index pages is
not reliable at all.
Firebird indexes are volatile. Index pages split when they get too full
and recombine when they get too empty. The splits and recombinations
don't lock the index - they never lock more than one page at a time.
Therefore, while you're walking an index, it can be changing in front of
you and in back of you.
Firebird's design requires that on-disk structures not cause deadlocks
when they are modified. A thread can hold only one page locked for
write at a time, and must generally be able to complete its changes and
release the page without acquiring a lock on another page. The
exception called a "hand off" and allows a threads to release one page
and get another if the pages follow a specific order. In the case of
indexes, that order is left to right - low to high.
As a result, the low to high path across an index will always be
correct, but the high to low path has to be fixed up later, and may, at
times, not be current - the back point may indicate a page that's no
longer part of the index - or even a page that's been released from one
part of the index and used in another.
Regards
Ann