Using CREATE INDEX

Go Up to Creating Indexes

The CREATE INDEX statement creates an index on one or more columns of a table. A single-column index searches only one column in response to a query, while a multi-column index searches one or more columns. Options specify:

The sort order for the index.
Whether duplicate values are allowed in the indexed column.

Use CREATE INDEX to improve speed of data access. For faster response to queries that require sorted values, use the index order that matches the query’s ORDER BY clause. Use an index for columns that appear in a WHERE clause to speed searching.

Note:
When working with encrypted columns, the MIN, MAX, BETWEEN and ORDER BY operations cannot use an index based on those fields due to the nature of the index key that is formed from the encrypted field value. So while the index is not useful for the above operations, it is still userful for equality matches and JOIN operations.

To improve index performance, use SET STATISTICS to recompute index selectivity, or rebuild the index by making it inactive, then active with sequential calls to ALTER INDEX. For more information about improving performance, see SET STATISTICS: Recomputing Index Selectivity.

The syntax for CREATE INDEX is:

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
 INDEX index ON table (col [, col ...]);

Preventing Duplicate Entries

No two rows can be alike when a UNIQUE index is specified for a column or set of columns. The system checks for duplicate values when the index is created, and each time a row is inserted or updated. InterBase automatically creates a UNIQUE index on a PRIMARY KEY column, forcing the values in that column to be unique identifiers for the row. Unique indexes only make sense when uniqueness is a characteristic of the data itself. For example, you would not define a unique index on a LAST_NAME column because there is a high probability for duplication. Conversely, a unique index is a good idea on a column containing a social security number.

To define an index that disallows duplicate entries, include the UNIQUE keyword in CREATE INDEX. The following statement creates a unique ascending index (PRODTYPEX) on the PRODUCT and PROJ_NAME columns of the PROJECT table:

CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);

Tip:
InterBase does not allow you to create a unique index on a column that already contains duplicate values. Before defining a UNIQUE index, use a SELECT statement to ensure there are no duplicate keys in the table. For example:

SELECT PRODUCT, PROJ_NAME FROM PROJECT
 GROUP BY PRODUCT, PROJ_NAME
 HAVING COUNT(*) > 1;

Specifying Index Sort Order

Specify a direction (low to high or high to low) by using the ASCENDING or DESCENDING keyword. By default, InterBase creates indexes in ascending order. To make a descending index on a column or group of columns, use the DESCENDING keyword to define the index. The following statement creates a descending index (DESC_X) on the CHANGE_DATE column of the SALARY_HISTORY table:

CREATE DESCENDING INDEX DESC_X ON SALARY_HISTORY (CHANGE_DATE);

Note:
To retrieve indexed data from this table in descending order, use ORDER BY CHANGE_DATE DESCENDING in the SELECT statement.

If you intend to use both ascending and descending sort orders on a particular column, define both an ascending and a descending index for the same column. The following example illustrates this:

CREATE ASCENDING INDEX ASCEND_X ON SALARY_HISTORY (CHANGE_DATE);
CREATE DESCENDING INDEX DESC_X ON SALARY_HISTORY (CHANGE_DATE);

Using Expression Index

InterBase 2017 Update 1 introduces Expression Index support, which can enhance the index definition and optimization of queries. For more information and examples refer to the next section: Expression Index

Advance To:

Expression Index