The Firebird 5.0 Language Reference | ALTER INDEX | Use Of ALTER INDEX On A Constraint Index

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

Deactivating the IDX_UPDATER index
```
ALTER INDEX IDX_UPDATER INACTIVE;
```
Switching the IDX_UPDATER index back to the active state and rebuilding it
```
ALTER INDEX IDX_UPDATER ACTIVE;
```

`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;

`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;

Use Of ALTER INDEX On A Constraint Index

Use of ALTER INDEX on a Constraint Index