FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 TABLEVIEW 

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

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

CREATE INDEX

Creates an index

Available in

DSQL, ESQL

Syntax
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
  INDEX indexname ON tablename
  {(col [, col ...]) | COMPUTED BY (<expression>)}
  [WHERE <search_condition>]
Table 1. CREATE INDEX Statement Parameters
Parameter Description

indexname

Index name.The maximum length is 63 characters

tablename

The name of the table for which the index is to be built

col

Name of a column in the table.Columns of the types BLOB and ARRAY and computed fields cannot be used in an index.

expression

The expression that will compute the values for a computed index, also known as an “expression index”

search_condition

Conditional expression of a partial index, to filter the rows to include in the index.

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.

Key Indexes

When a primary key, foreign key or unique constraint is added to a table or column, an index with the same name is created automatically, without an explicit directive from the designer.For example, the PK_COUNTRY index will be created automatically when you execute and commit the following statement:

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

docnext count = 21

Who Can Create an Index?

The CREATE INDEX statement can be executed by:

  • Administrators

  • The owner of the table

  • Users with the ALTER ANY TABLE privilege

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

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

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

Tip

A descending index can be useful on a column that will be subjected to searches on the high values (“newest”, maximum, etc.)

Firebird uses B-tree indexes, which are bidirectional.However, due to technical limitations, Firebird uses an index in one direction only.

See also Firebird for the Database Expert: Episode 3 - On disk consistency

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.

Note

Expression indexes can also be used as a workaround for indexing computed columns: use the name of the computed column as the expression.

Limits on Indexes

Certain limits apply to indexes.

The maximum length of a key in an index is limited to a quarter 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.

Table 1. Maximum Indexes per Table

Page Size

Number of Indexes Depending on Column Count

Single

2-Column

3-Column

4096

203

145

113

8192

408

291

227

16384

818

584

454

32768

1637

1169

909

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, the character set, and the collation.

Table 1. Maximum indexable (VAR)CHAR length

Page Size

Maximum Indexable String Length by Charset Type

1 byte/char

2 byte/char

3 byte/char

4 byte/char

4096

1015

507

338

253

8192

2039

1019

679

509

16384

4087

2043

1362

1021

32768

8183

4091

2727

2045
Note

Depending on the collation, the maximum size can be further reduced as case-insensitive and accent-insensitive collations require more bytes per character in an index.See also Character Indexes in Chapter Data Types and Subtypes.

Parallelized Index Creation

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.

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

  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 also

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

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

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]