5.5. INDEX

An index is a database object used for faster data retrieval from a table or for speeding up the sorting of query. Indexes are used also to enforce the refererential integrity constraints PRIMARY KEY, FOREIGN KEY and UNIQUE.

This section describes how to create indexes, activate and deactivate them, delete them and collect statistics (recalculate selectivity) for them.

5.5.1. CREATE INDEX

Used for

Creating an index for a table

Available in

DSQL, ESQL

Syntax

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
  INDEX indexname ON tablename
  {(col [, col …]) | COMPUTED BY (<expression>)}

The CREATE INDEX statement creates an index for a table that can be used to speed up searching, sorting and grouping. Indexes are created automatically in the process of defining constraints, such as primary key, foreign key or unique constraints.

An index can be built on the content of columns of any data type except for BLOB and arrays. The name (identifier) of an index must be unique among all index names.

ALTER TABLE COUNTRY ADD CONSTRAINT PK_COUNTRY
  PRIMARY KEY (ID);

Unique Indexes

Specifying the keyword UNIQUE in the index creation statement creates an index in which uniqueness will be enforced throughout the table. The index is referred to as a “unique index”. A unique index is not a constraint.

Unique indexes cannot contain duplicate key values (or duplicate key value combinations, in the case of compound, or multi-column, or multi-segment) indexes. Duplicated NULLs are permitted, in accordance with the SQL:99 standard, in both single-segment and multi-segment indexes.

Index Direction

All indexes in Firebird are uni-directional. An index may be constructed from the lowest value to the highest (ascending order) or from the highest value to the lowest (descending order). The keywords ASC[ENDING] and DESC[ENDING] are used to specify the direction of the index. The default index order is ASC[ENDING]. It is quite valid to define both an ascending and a descending index on the same column or key set.

Computed (Expression) Indexes

In creating an index, you can use the COMPUTED BY clause to specify an expression instead of one or more columns. Computed indexes are used in queries where the condition in a WHERE, ORDER BY or GROUP BY clause exactly matches the expression in the index definition. The expression in a computed index may involve several columns in the table.

Limits on Indexes

Certain limits apply to indexes.

The maximum length of a key in an index is limited to ¼ of the page size.

Maximum Indexes per Table

The number of indexes that can be accommodated for each table is limited. The actual maximum for a specific table depends on the page size and the number of columns in the indexes.

Character Index Limits

The maximum indexed string length is 9 bytes less than the maximum key length. The maximum indexable string length depends on the page size and the character set.

Only the table owner and administrators have the authority to use CREATE INDEX.

Examples Using CREATE INDEX

1. Creating an index for the UPDATER_ID column in the SALARY_HISTORY table

   CREATE INDEX IDX_UPDATER
     ON SALARY_HISTORY (UPDATER_ID);

2. Creating an index with keys sorted in the descending order for the CHANGE_DATE column in the SALARY_HISTORY table

   CREATE DESCENDING INDEX IDX_CHANGE
     ON SALARY_HISTORY (CHANGE_DATE);

3. Creating a multi-segment index for the ORDER_STATUS, PAID columns in the SALES table

   CREATE INDEX IDX_SALESTAT
     ON SALES (ORDER_STATUS, PAID);

4. Creating an index that does not permit duplicate values for the NAME column in the COUNTRY table

   CREATE UNIQUE INDEX UNQ_COUNTRY_NAME
     ON COUNTRY (NAME);

5. Creating a computed index for the PERSONS table

   CREATE INDEX IDX_NAME_UPPER ON PERSONS
     COMPUTED BY (UPPER (NAME));

   An index like this can be used for a case-insensitive search:

   SELECT *
   FROM PERSONS
   WHERE UPPER(NAME) STARTING WITH UPPER('Iv');

See also

ALTER INDEX, DROP INDEX

5.5.2. ALTER INDEX

Used for

Activating or deactivating an index; rebuilding an index

Available in

DSQL, ESQL

Syntax

ALTER INDEX indexname {ACTIVE | INACTIVE}

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

• 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 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 exactly the same for CREATE INDEX.

  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.

• 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.

  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 houskeeping to do, occasionally, on the indexes of a large table in a database that has frequent inserts, updates or deletes but is infrequently restored.

Use of ALTER INDEX on a Constraint Index

Altering the enforcing 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.

Only the table owner and administrators have the authority to use ALTER INDEX.

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

CREATE INDEX, DROP INDEX, SET STATISTICS

5.5.3. DROP INDEX

Used for

Deleting an index

Available in

DSQL, ESQL

Syntax

DROP INDEX indexname

The DROP INDEX statement deletes the named index from the database.

Only the table owner and administrators have the authority to use DROP INDEX.

DROP INDEX Example

Deleting the IDX_UPDATER index

DROP INDEX IDX_UPDATER;

See also

CREATE INDEX, ALTER INDEX

5.5.4. SET STATISTICS

Used for

Recalculating the selectivity of an index

Available in

DSQL, ESQL

Syntax

SET STATISTICS INDEX indexname

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

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.

The selectivity of an index can be recalculated by the owner of the table or an administrator. It can be performed under concurrent load without risk of corruption. However, be aware that, 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

CREATE INDEX, ALTER INDEX