Altering an Index - Firebird

Activating/Deactivating

The ALTER INDEX statement is used to switch the state of an index from active to inactive and vice versa. It can be used to switch off indexing before inserting or updating a large batch of rows and avoid the overhead of maintaining the indexes during the long operation. After the operation, indexing can be reactivated and the indexes will be rebuilt.

Its other use is a housekeeping one. The distribution of values changes, gradually under normal conditions and, under some operating conditions, more frequently. The binary tree structures in which indexes are maintained can become unbalanced. Switching an index from active to inactive and back to active rebuilds and rebalances it.

The syntax is

ALTER INDEX index-name INACTIVE | ACTIVE ;

“Index Is in Use” Error

An index that is being used in a transaction cannot be altered or dropped until the transaction has finished using it. Attempts will have different results, according to the lock setting of the active transaction:

  • In a WAIT transaction, the ALTER INDEX operation waits until the transaction completes.
  • In a NOWAIT transaction, Firebird returns an error.

Altering the Structure of an Index

Unlike many ALTER statements, the ALTER INDEX syntax cannot be used to alter the structure of the object. For this, it is necessary to drop the index and define it a new, using CREATE INDEX.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics