Norm,

> I've no idea how good a suggestion this is, but how difficult (sorry
> developers!) would it be to do something along the Oracle lines?
>
> RDB$RELATIONS would have columns for cardinality, spread of values and
> so on. These would be recalculated on demand - Oracle used a package
> called DBMS_STATS to gather optimiser stats.
>
> The optimiser in Oracle then reads these stats, and histograms etc form
> other system tables, and uses these to pick the best execution plan.
>
> Granted the stats have to be kept "reasonably" up to date or performance
> suffers, but it's a good way of avoiding dynamic scans to check
> cardinality?
>

That way, if the stats are wrong it's the database administrator's fault
that
the performance is bad. Knowing how to fix that bad performance makes
the DBA more magical, raising his value.

I really prefer self-tuning, self-managing statistics. We don't have them
for
indexes, but as I said, index selectivity is not normally as volatile as
cardinality.

There's problem with keeping data ranges (or histograms), given that
Firebird prepares a statement before it knows the values of parameters.

select first_name, last_name from rugby_players where gender = ?

Assuming an index on gender, you'd handle the query one way if the
? = M vs. F

And if it happens during a nested loop join? Re-optimize on every record?

Good luck (and we all need it)

Ann


[Non-text portions of this message have been removed]