The CREATE INDEX statement can be executed by:

• Administrators

• The owner of the table

• Users with the ALTER ANY TABLE privilege

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 standard, in both single-segment and multi-segment indexes.

Specifying the WHERE clause in the index creation statement creates a partial index (also knows as filtered index). A partial index contains only rows that match the search condition of the WHERE.

A partial index definition may include the UNIQUE clause. In this case, every key in the index is required to be unique. This allows enforcing uniqueness for a subset of table rows.

A partial index is usable only in the following cases:

• The WHERE clause of the statement includes exactly the same boolean expression as the one defined for the index;

• The search condition defined for the index contains ORed boolean expressions and one of them is explicitly included in the WHERE clause of the statement;

• The search condition defined for the index specifies IS NOT NULL and the WHERE clause of the statement includes an expression on the same field that is known to exclude NULLs.

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 valid to define both an ascending and a descending index on the same column or key set.

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.

Certain limits apply to indexes.

• The maximum length of a key in an index is limited to a quarter of the page size

• A compound index can have at most 16 columns

Since Firebird 5.0, index creation can be parallelized. Parallelization happens automatically if the current connection has two or more parallel workers — configured through ParallelWorkers in firebird.conf or isc_dpb_parallel_workers — and the server has parallel workers available.

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');

6. Creating a partial index and using its condition:

   CREATE INDEX IT1_COL ON T1 (COL) WHERE COL < 100;
   SELECT * FROM T1 WHERE COL < 100;
   -- PLAN (T1 INDEX (IT1_COL))

7. Creating a partial index which excludes NULL

   CREATE INDEX IT1_COL2 ON T1 (COL) WHERE COL IS NOT NULL;
   SELECT * FROM T1 WHERE COL > 100;
   PLAN (T1 INDEX IT1_COL2)

8. Creating a partial index with ORed conditions

   CREATE INDEX IT1_COL3 ON T1 (COL) WHERE COL = 1 OR COL = 2;
   SELECT * FROM T1 WHERE COL = 2;
   -- PLAN (T1 INDEX IT1_COL3)

9. Using a partial index to enforce uniqueness for a subset of rows

   create table OFFER (
     OFFER_ID bigint generated always as identity primary key,
     PRODUCT_ID bigint not null,
     ARCHIVED boolean default false not null,
     PRICE decimal(9,2) not null
   );
    
   create unique index IDX_OFFER_UNIQUE_PRODUCT
     on OFFER (PRODUCT_ID)
     where not ARCHIVED;
    
   insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, false, 18.95);
   insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, true, 17.95);
   insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, true, 16.95);
   -- Next fails due to second record for PRODUCT_ID=1 and ARCHIVED=false:
   insert into OFFER (PRODUCT_ID, ARCHIVED, PRICE) values (1, false, 19.95);
   -- Statement failed, SQLSTATE = 23000
   -- attempt to store duplicate value (visible to active transactions) in unique index "IDX_OFFER_UNIQUE_PRODUCT"
   -- -Problematic key value is ("PRODUCT_ID" = 1)

See alsoSection 5.5.2, “ALTER INDEX”, Section 5.5.3, “DROP INDEX”

The ALTER INDEX statement can be executed by:

• Administrators

• The owner of the table

• Users with the ALTER ANY TABLE privilege

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.

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 alsoSection 5.5.1, “CREATE INDEX”, Section 5.5.3, “DROP INDEX”, Section 5.5.4, “SET STATISTICS”

The DROP INDEX statement can be executed by:

• Administrators

• The owner of the table

• Users with the ALTER ANY TABLE privilege

Dropping the IDX_UPDATER index

DROP INDEX IDX_UPDATER;

See alsoSection 5.5.1, “CREATE INDEX”, Section 5.5.2, “ALTER INDEX”

The SET STATISTICS statement can be executed by:

• Administrators

• The owner of the table

• Users with the ALTER ANY TABLE privilege

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 statements CREATE INDEX and ALTER INDEX ACTIVE both store index statistics that correspond to the contents of the newly-[re]built index.

SET STATISTICS 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.

Recalculating the selectivity of the index IDX_UPDATER

SET STATISTICS INDEX IDX_UPDATER;

See alsoSection 5.5.1, “CREATE INDEX”, Section 5.5.2, “ALTER INDEX”