ALTER INDEX: Deactivating an Index

From InterBase
Jump to: navigation, search

Go Up to Improving Index Performance


The ALTER INDEX statement deactivates and reactivates an index. Deactivating and reactivating an index is useful when changes in the distribution of indexed data cause the index to become unbalanced.

To rebuild the index, first use ALTER INDEX INACTIVE to deactivate the index, then set ALTER INDEX ACTIVE to reactivate it again. This method recreates and balances the index.

Note: You can also rebuild an index by backing up and restoring the database with the gbak utility. gbak stores only the definition of the index, not the data structure, so when you restore the database, gbak rebuilds the indexes.
Tip: Before inserting a large number of rows, deactivate a table’s indexes during the insert, then reactivate the index to rebuild it. Otherwise, InterBase incrementally updates the index each time a single row is inserted.

The syntax for ALTER INDEX is:

ALTER INDEX name
{ACTIVE | INACTIVE};

The following statements deactivate and reactivate an index to rebuild it:

ALTER INDEX BUDGETX INACTIVE;
ALTER INDEX BUDGETX ACTIVE;
Note: The following restrictions apply to altering an index:
  • In order to alter an index, you must be the creator of the index, a SYSDBA user, or a user with operating system root privileges.
  • You cannot alter an index if it is in use in an active database. An index is in use if it is currently being used by a compiled request to process a query. All requests using an index must be released to make it available.
  • You cannot alter an index that has been defined with a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint. If you want to modify the constraints, you must use ALTER TABLE. For more information about ALTER TABLE, see the Language Reference.
  • You cannot use ALTER INDEX to add or drop index columns or keys. Use DROP INDEX to delete the index and then redefine it with CREATE INDEX.

Advance To: