Is it possible to deactivate index in Firebird without dropping it?

Question

From the ib-aid link at RDB$INDICES System Table the RDB$INDICES has a column RDB$INDEX_INACTIVE column which indicates active/inactive status. Is it possible to modify this column to activate or deactivate an index. Could this be done instead of actually dropping/adding an index?

Answer 1

Yes, you can use ALTER INDEX <indexname> {ACTIVE | INACTIVE}, see the documentation for ALTER INDEX in the Firebird 4.0 Language Reference. This statement is available in all Firebird versions.

For INACTIVE:

With the INACTIVE option, the index is switched from the active to inactive state. The effect is similar to the DROP INDEX statement except that the index definition remains in the database. Altering a constraint index to the inactive state is not permitted.

This last thing is important: you cannot do this for indexes backing a constraint.

For ACTIVE:

With the ACTIVE option, if the index is in the inactive state, it will be switched to active state and the system rebuilds the index.

Altering an already active index to ACTIVE will also rebuild the index.