FirebirdSQL logo
 TABLEVIEW 

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.

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.