At 10:57 AM 7/23/2004, Salvatore Besso wrote:

>Should selectivity recomputation be performed periodically for best
>performance?

Yes

>If yes at which intervals?

It depends.

>Should indexes be rebuilt periodically for best performance also?

Usually, no.

To be more helpful, the statistics for an index are the ratio between
the number of distinct values and the total number of values. If
your data doesn't change much, then you don't need to refresh the
statistics much. Running "set statistics" is not terribly expensive,
and I would probably set up a script that ran through all non-unique
indexes updating their statistics after every backup. If that proved
too time-consuming, I'd examine index statistics for each index before
and after a reset and stop resetting those that don't change much.
Maybe I'd run the full script monthly, just to be sure.

As for rebuilding indexes, Firebird indexes are self-balancing and
combine nodes as their fill level drops below a threshold, so in
theory, rebuilding should never be necessary. In practice, I'd
run a periodic (weekly?) gstat, looking for indexes with a fill level
below 65% and rebuild those indexes. Depending on activity, you
may need to check for low fill levels more - or less - often.

Regards,


Ann