<< Recreate Database | IBExpert | Recompile all stored procedures and triggers >>

Tutorial

Indices statistics are used by the Firebird/InterBase^® Optimizer, to determine which index is the most efficient. All statistics are recalculated only when a database is restored after backing up, or when this is explicitly requested by the developer. (This feature is unfortunately not included in the free IBExpert Personal Edition.)

When an index is initially created, its statistical value is 0. Therefore it is extremely important, particularly with new databases where the first data sets are being entered, to regularly explicitly recompute the selectivity, so that the optimizer can recognize the most efficient indices. This is not so important with databases, where little data manipulation occurs, as the selectivity will change very little.

To recompute the selectivity of all indices use the IBExpert menu item Recompute Selectivity of all Indices. This can be found in the IBExpert Database menu or using the right mouse button in the DB Explorer.

v

You do not need to shut down the database to recompute the selectivity of indices.

Individual indices can be recomputed directly in the Index Editor, in the SQL Editor on the Plan Analyzer page (simply click the Recompute selectivity button), or manually in the SQL Editor using the command:

 SET STATISTICS INDEX <index_name>;

Single or multiple indices can also be recomputed directly in the Index Editor and the Table Editor / Indices page, using the right-click menu.

The same Recomputing Selectivity dialog as above is then displayed.

The new statistical values can be viewed for individual tables in the Index Editor and the Table Editor on the Indices page (providing the statistics are blended in using the right-click menu item Show Statistics).

See also:
Index
SQL Editor / Plan Analyzer
SQL Editor / Performance Analysis
Database Statistics / Indices
Firebird for the database expert: Episode 1 - Indexes
Firebird 2.0.4 Release Notes: Enhancements to indexing
Firebird administration using IBExpert - Index statistics
Firebird administration using IBExpert - Automating the recalculation of index statistics

back to top of page
<< Recreate Database | IBExpert | Recompile all stored procedures and triggers >>