>On 31 Jan 2002, at 14:29, Carlo Pires wrote:
> >
> > select MAX(COD_VALUE) from TABLE_TEST;
> >
> > The plan used by firebird is NATURAL. This is strange to me because I
> have a
> > index for field COD_VALUE. I had to implement a stored procedure + a
> > descending index for field COD_VALUE
First, for Carlo,
Why did you need a stored procedure?
At 02:19 PM 2/1/2002 +0100, Pavel Cisar wrote:
>Yes, it's know behaviour :-) It's because index keys in IB/FB are
>stored as difference from previous key, so index is traversable only
>in one direction. You'll have to go through whole ASC index to find
>max value, so index is not used (too much I/O). Hence the rule
>ASC for MIN and DESC for MAX :-)
It's not just compression. The first key on each index page is
uncompressed, so you just have to walk one page in order. Another
part of the problem is that the index can hold values that are not
applicable to the current transaction, so you have to verify that
the actual record is ok, not just the index entry. And here we
get into the tricky bit.
To avoid deadlocks, each transaction is guaranteed only one
page lock. It can take more, but must be prepared to give up
all but one. Reading the data page means that the lock on the
index page must be available if some other transaction wants it.
If another transaction grabs it, then the one looking for the
MAX value has to start over from the top of the index.
This problem is reasonably easy to solve in SuperServer where
there is one cache manager who can "know" if a page is still
OK. In classic, I know of no good solution.
Regards,
Ann
|
