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. InterBase calculates index selectivity automatically only when an index is created or activated, or under user request using SET STATISTICS INDEX <index_name>. Bulk data updates on the underlying table can put an index selectivity value out of sync with reality.

The ris.sql SQL script helps users recompute index selectivity on demand for various sets of indices via the stored procedure COMPUTE_INDEX_SELECTIVITY which recomputes index selectivity for a given range of indices. You can find ris.sql on %ProgramData%\Embarcadero\InterBase\gds_db\examples. The stored procedure accepts the following parameters:

index_scope (string) can accept the following values:

DATABASE - All user indices for the whole database.

TABLE - All indices for a given table name.

INDEX - Just the given index name.

SYSTEM - All system indices except for the ones on RDB$ENCRYPTIONS.

Run the following as SYSDSO user if you want to reset selectivity on RDB$ENCRYPTIONS indices.

UPDATE RDB$INDICES SET RDB$STATISTICS = -1.0 
WHERE RDB$RELATION_NAME = 'RDB$ENCRYPTIONS'
COMMIT;

entity_name - Name of the table or index. If you are not using quoted identifier names, make sure you provide the entity name in upper case ASCII. This should be NULL if the index_scope specified is database-wide ('DATABASE', or, 'SYSTEM')

Make sure you execute a COMMIT after executing the stored procedure.

Limitations

• You need to have database ownership/sysdba rights to execute this procedure for the whole database.
• Since the stored procedure uses EXECUTE STATEMENT, this script can only be used with InterBase XE or later versions.

Examples

• Recompute selectivity for all user indices in the database.

EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('DATABASE', NULL);
COMMIT;

• Recompute selectivity for a particular table. This is useful when a particular table has undergone bulk data insert/update/delete operation.

EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('TABLE', 'EMPLOYEE');
COMMIT;

• Recompute selectivity for a particular index.

EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('INDEX', 'RDB$PRIMARY2');
COMMIT;

• Recompute selectivity for all system indices in the database.

EXECUTE PROCEDURE COMPUTE_INDEX_SELECTIVITY ('SYSTEM', NULL);
COMMIT;

• SQL to check index selectivity for user indices.

select cast (RDB$RELATION_NAME as varchar(32)) AS TABLE_NAME, 
       cast (RDB$INDEX_NAME as varchar(32)) AS INDEX_NAME, 
       RDB$STATISTICS 
  from rdb$indices 
 where coalesce (rdb$system_flag, 0) = 0 
 order by RDB$STATISTICS;

Advance To:

• Performing Regular Backups