At 11:40 AM 4/28/00 +0200, Paul Reeves wrote:

> >
> > Paul Reeves wrote:
> > > I have often wondered why the engine doesn't automatically recompute
> > > these values.

Computing the selectivity of an index requires reading the leaf
level from beginning to end. The only time that's done, during
normal processing, is to support an "order by" of the whole
table. I suppose that the selectivity could be reset when such
an operation occurred.

>Except for one very common situation. Create your database. Populate it with
>data. Start using it. Then check the selectivity. You will find that almost
>every index will have 0 - the selectivity that existed when the db was
>created.
>Of course a backup and restore will fix this.

As with deactivating and reactivating the indexes (which can have
secondary benefits) or just resetting the selectivity.

>How much significance all this has depends on the question 'How much credence
>does the optimiser give to the selectivity as stored in rdb$indices.'


>If you
>look at the output of gstat it is evident that the index stores a close
>approximation of the actual number of unique values ( nodes - total_dup ).
>Therefore it can ignore the rdb$statisitics column entirely.

Actually, gstat does a pass over the index and creates those numbers.
They're not stored in the index itself.

>My guess (hope) is that the optimiser will start with the rdb$statistics
>column
>and revise its plan upon actually looking at the indexes chosen for the
>plan if
>there is a wild divergence. Assuming, that is, that looking up
>rdb$statistics is
>quicker than looking at the cached index structures.

No. The plan is chosen before the index is examined.

>It seems to me that if rdb$statistics is important then it should be
>maintained,
>especially under super server. What else are background, low priority threads
>for?

As long as it doesn't wipe out the cache.

Ann