At 01:23 AM 4/29/00 -0400, Claudio Valderrama C. wrote:

>
> Activating/deactivating the indexes seems to be a more complete solution
>than resetting selectivity, right or wrong? It seems that with selectivity
>recalculation, there's a chance to get unbalances indexes (page 26 of IB6's
>LangRef; I don't know if this has a real impact) whereas switching
>active/inactive state yields balanced indexes.
> And now on the subject of being able to deactivate indexes produced by
>PK/UK/FK declarations. :-)
> I wonder if this description in LangRef about SET STATISTICS is accurate:
>«Index selectivity is a calculation, based on the number of distinct rows in
>a table, that is made by the InterBase optimizer when a table is accessed.
>It is cached in memory, where the optimizer can access it to calculate the
>optimal retrieval plan for a given query.» But Jim said:
>«Design the beast so its insenstive to selectivities
>(it mostly is -- you guys are barking up the wrong tree).»
>

Here is the unofficial official scoop on selectivity:

Selectivity is computed during index creation (fast_load). It
is stored as part of the index description on the index root
page (IRT). It is also computed by BTR_selectivity which
traverses the index leaf pages.

Selectivity is referenced in exact one place in the optimizer
to estimate the cardinality resulting from a potential partial
join to compute least cost join order.

There are cases where a more accurate selectivity will produce
a faster join, but they are few and far between. Bad optimization
is usually the result of bugs in the optimizer, not bad input.

Please note the selectivity is never used to determine whether
or not an index will be used.

Jim Starkey