Tuning Indexes

Go Up to Database Tuning Tasks

Periodic maintenance of indexes can improve their performance benefit. You can write SQL scripts to automate these tasks. See Using SQL Scripts.

Rebuilding Indexes

Periodically, a B-tree data structure might become imbalanced, or it might have some values in the tree that have been deleted from the database (this should not happen in InterBase versions later than 5, due to index garbage collection).

You should periodically rebuild indexes by turning them off and on:

ALTER INDEX name INACTIVE;
ALTER INDEX name ACTIVE;

Recalculating Index Selectivity

The selectivity of an index is an indicator of its uniqueness. The optimizer uses selectivity in its cost-based analysis algorithm when deciding whether to use a given index in a query execution plan. If the selectivity is out of date and does not accurately represent the state of the index, the optimizer might use or discount the index inappropriately. This does usually not have a great performance penalty unless the selectivity is highly out of date.

You should recalculate the index selectivity if a change to the table affects the average distribution of data values:

SET STATISTICS INDEX name;