Tetram Corp wrote:

> considering your answer, is it correct to say that SET STATISTICS
> requests are now useless?
>

No - as Helen said, "balance" is a physical attribute of an index and
indicates how deep the index is - that is, how many pages must be read
to go from the top of the index to the bottom. Statistics are logical
and reflect the number of distinct values in the index vs. the total
number of entries.

Eons ago, before Jim started writing relational databases, he
worked on a query language that ran against ISAM files and CODASYL
databases. It included an optimizer so Jim wanted to know how
many records were in each file or record type... The CODASYL system
kept statistics, the ISAM system didn't. The project lead of the
ISAM project didn't want to keep statistics. Jim said they could
be approximate. He said, "OK, then thirteen." Later profiling
showed that under load, the CODASYL system spent about 10% of its
time maintaining statistics or waiting for other transactions to
finish updating statistics.

The world is very different now, but Firebird still keeps fuzzy
optimization statistics - for cardinality, it uses a rough page
count. For selectivity, it uses the selectivity set when the
index was created. The latter is OK if you store data then
create indexes because subsequent data is likely to follow the
rough selectivity of the original data. It's not good at all
if you create indexes before you store data. That's when
SET STATISTICS is your friend.


Regards,


Ann
indexes then store data.