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.