FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 TABLEVIEW 

ALTER INDEX

Activates or deactivates an index, and rebuilds an index

Available in

DSQL, ESQL

Syntax
ALTER INDEX indexname {ACTIVE | INACTIVE}
Table 1. ALTER INDEX Statement Parameter
Parameter Description

indexname

Index name

The ALTER INDEX statement activates or deactivates an index.There is no facility on this statement for altering any attributes of the index.

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.

An active index can be deactivated if there are no queries prepared using that index;otherwise, an “object in use” error is returned.

Activating an inactive index is also safe.However, if there are active transactions modifying the table, the transaction containing the ALTER INDEX statement will fail if it has the NOWAIT attribute.If the transaction is in WAIT mode, it will wait for completion of concurrent transactions.

On the other side of the coin, if our ALTER INDEX succeeds and starts to rebuild the index at COMMIT, other transactions modifying that table will fail or wait, according to their WAIT/NO WAIT attributes.The situation is the same for CREATE INDEX.

Note
How is it Useful?

It might be useful to switch an index to the inactive state whilst inserting, updating or deleting a large batch of records in the table that owns the index.
ACTIVE

Rebuilds the index (even if already active), and marks it as active.

Note
How is it Useful?

Even if the index is active when ALTER INDEX …​ ACTIVE is executed, the index will be rebuilt.Rebuilding indexes can be a useful piece of housekeeping to do, occasionally, on the indexes of a large table in a database that has frequent inserts, updates or deletes but is infrequently restored.

Who Can Alter an Index?

The ALTER INDEX statement can be executed by:

  • Administrators

  • The owner of the table

  • Users with the ALTER ANY TABLE privilege

docnext count = 9

Use of ALTER INDEX on a Constraint Index

Altering the index of a PRIMARY KEY, FOREIGN KEY or UNIQUE constraint to INACTIVE is not permitted.However, ALTER INDEX …​ ACTIVE works just as well with constraint indexes as it does with others, as an index rebuilding tool.

ALTER INDEX Examples

  1. Deactivating the IDX_UPDATER index

    ALTER INDEX IDX_UPDATER INACTIVE;

  2. Switching the IDX_UPDATER index back to the active state and rebuilding it

    ALTER INDEX IDX_UPDATER ACTIVE;
See also

[fblangref50-ddl-idx-create], [fblangref50-ddl-idx-dropidx], [fblangref50-ddl-idx-setsttstcs]

DROP INDEX

Drops an index

Available in

DSQL, ESQL

Syntax
DROP INDEX indexname
Table 1. DROP INDEX Statement Parameter
Parameter Description

indexname

Index name

The DROP INDEX statement drops (deletes) the named index from the database.

Note

A constraint index cannot be dropped using DROP INDEX.Constraint indexes are dropped during the process of executing the command ALTER TABLE …​ DROP CONSTRAINT …​.

Who Can Drop an Index?

The DROP INDEX statement can be executed by:

  • Administrators

  • The owner of the table

  • Users with the ALTER ANY TABLE privilege

DROP INDEX Example

Dropping the IDX_UPDATER index
DROP INDEX IDX_UPDATER;
See also

[fblangref50-ddl-idx-create], [fblangref50-ddl-idx-altridx]

SET STATISTICS

Recalculates the selectivity of an index

Available in

DSQL, ESQL

Syntax
SET STATISTICS INDEX indexname
Table 1. SET STATISTICS Statement Parameter
Parameter Description

indexname

Index name

The SET STATISTICS statement recalculates the selectivity of the specified index.

Who Can Update Index Statistics?

The SET STATISTICS statement can be executed by:

  • Administrators

  • The owner of the table

  • Users with the ALTER ANY TABLE privilege

Index Selectivity

The selectivity of an index is the result of evaluating the number of rows that can be selected in a search on every index value.A unique index has the maximum selectivity because it is impossible to select more than one row for each value of an index key if it is used.Keeping the selectivity of an index up to date is important for the optimizer’s choices in seeking the most optimal query plan.

Index statistics in Firebird are not automatically recalculated in response to large batches of inserts, updates or deletions.It may be beneficial to recalculate the selectivity of an index after such operations because the selectivity tends to become outdated.

Note

The statements CREATE INDEX and ALTER INDEX ACTIVE both store index statistics that correspond to the contents of the newly-[re]built index.

It can be performed under concurrent load without risk of corruption.However, under concurrent load, the newly calculated statistics could become outdated as soon as SET STATISTICS finishes.

Example Using SET STATISTICS

Recalculating the selectivity of the index IDX_UPDATER
SET STATISTICS INDEX IDX_UPDATER;
See also

[fblangref50-ddl-idx-create], [fblangref50-ddl-idx-altridx]