ALTER INDEX: Deactivating an Index
From InterBase
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.
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.
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:
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, orFOREIGN KEYconstraint. If you want to modify the constraints, you must useALTER TABLE. For more information aboutALTER TABLE, see the Language Reference. - You cannot use
ALTER INDEXto add or drop index columns or keys. UseDROP INDEXto delete the index and then redefine it withCREATE INDEX.