Re: [Firebird-test] select MAX() not used INDEXES to run

[Pavel C. <pc...@us...>]

Hi,

On 31 Jan 2002, at 14:29, Carlo Pires wrote:

> Hi,
> 
> I'm testing firebird with a table with 65000 records. When I try to:
> 
> 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 to get, more quickly, the max value for
> COD_VALUE.
> 
> Know anyone about this? I'm wrong ?

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 a trade off -> more keys on page -> shallow index tree -> faster 
index traversal, but limited index usage for MIN and MAX use.

Best regards

Pavel Cisar
http://www.ibphoenix.com
For all your upto date Firebird and
InterBase information