Markus Kemper wrote:

>
>
> SET STATISTICS INDEX <index_name>
>
> It says: For tables where the number of duplicate values
> in indexed columns radically increases or decreases,
> periodically recomputing index selectivity can
> improve performance.
>
> NOTE: SET STATISTICS does _not_ rebuild an index.
>

I have often wondered why the engine doesn't automatically recompute these
values. They are stored in rdb$indices. It seems to me that the indexes
themselves know their selectivity so when they diverge wildy from that stored in
the system table they could be resynched automatically.

Additionally, given that the indexes 'know' their selectivity why/how does the
selectivity column in rdb$indices come into play? I would have thought that the
optimizer would ignore it, or downplay its' significance somewhat given that it
is usually stale and unreliable data.

Of course, I could have this totally wrong. I am making assumptions based on the
output from gstat. It is possibly quicker for the query optimiser to start with
a system table lookup than to interrogate the index pages themselves.

Paul
--

Paul Reeves
Fleet River Software