SET STATISTICS: Recomputing Index Selectivity

From InterBase
Jump to: navigation, search

Go Up to Improving Index Performance


For tables where the number of duplicate values in indexed columns radically increases or decreases, periodically recomputing index selectivity can improve performance. SET STATISTICS recomputes the selectivity of an index.

Index selectivity is a calculation that is made by the InterBase optimizer when a table is accessed, and is based on the number of distinct rows in a table. It is cached in memory, where the optimizer can access it to calculate the optimal retrieval plan for a given query.

The syntax for SET STATISTICS is:

SET STATISTICS INDEX name;

The following statement recomputes the selectivity for an index:

SET STATISTICS INDEX MINSALX;
Note: The following restrictions apply to the SET STATISTICS statement:
  • In order to use SET STATISTICS, you must be the creator of the index, a SYSDBA user, or a user with operating system root privileges.
  • SET STATISTICS does not rebuild an index. To rebuild an index, use ALTER INDEX.

Advance To: