ALTER INDEX
Go Up to Statement and Function Reference (Language Reference Guide)
Activates or deactivates an index. Available in embedded SQL, DSQL, and isql, but not in the stored procedure or trigger language.
ALTER INDEX <name> {ACTIVE | INACTIVE};
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in
isql, the semicolon is a terminating symbol for the statement, so it must be included.| Argument | Description |
|---|---|
|
name |
Name of an existing index. |
|
|
Changes an |
|
|
Changes an |
Description: ALTER INDEX makes an inactive index available for use, or disables the use of an active index. Deactivating an index is exactly like dropping it, except that the index definition remains in the database. Activating an index creates a new index structure.
Before inserting, updating, or deleting a large number of rows, deactivate a table’s indexes to avoid altering the index incrementally. When finished, reactivate the index. A reasonable metric is that if you intend to add or delete more than 15% of the rows in a table, or update an indexed column in more than 10% of the rows, you should consider deactivating and reactivating the index.
If an index is in use, ALTER INDEX does not take effect until the index is no longer in use.
ALTER INDEX fails and returns an error if the index is defined for a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint. To alter such an index, use DROP INDEX to delete the index, then recreate it with CREATE INDEX.
An index can be altered by its creator, the SYSDBA user, and any users with operating system root privileges.
To add or drop index columns or keys, use DROP INDEX to delete the index, then recreate it with CREATE INDEX.
Example: The following isql statements deactivate and reactivate an index to rebuild it:
ALTER INDEX BUDGETX INACTIVE;
ALTER INDEX BUDGETX ACTIVE;