Creating an Index
Go Up to Creating Metadata
SQL provides CREATE INDEX for establishing user-defined database indexes. An index, based on one or more columns in a table, is used to speed data retrieval for queries that access those columns. The syntax for CREATE INDEX is:
EXEC SQL CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX <index> ON table (col [, col ...]);
For example, the following statement defines an index, NAMEX, for the LAST_NAME and FIRST_NAME columns in the EMPLOYEE table:
EXEC SQL CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);
InterBase automatically generates system-level indexes when tables are defined using
UNIQUE and PRIMARY KEY constraints. For more information about constraints, see the Data Definition Guide.See the Language Reference Guide for more information about CREATE INDEX syntax.
Preventing Duplicate Index Entries
To define an index that eliminates duplicate entries, include the UNIQUE keyword in CREATE INDEX. The following statement creates a unique index, PRODTYPEX, on the PROJECT table:
EXEC SQL CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);
After a unique index is defined, users cannot insert or update values in indexed columns if those values already exist there. For unique indexes defined on multiple columns, like
PRODTYPEX in the previous example, the same value can be entered within individual columns, but the combination of values entered in all columns defined for the index must be unique.Specifying Index Sort Order
By default, SQL stores an index in ascending order. To make a descending sort on a column or group of columns more efficient, use the DESCENDING keyword to define the index. For example, the following statement creates an index, CHANGEX, based on the CHANGE_DATE column in the SALARY_HISTORY table:
EXEC SQL CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY (CHANGE_DATE);
To retrieve indexed data in descending order, use
ORDER BY in the SELECT statement to specify retrieval order.