Creating an Index

From InterBase
Jump to: navigation, search

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);
Note: 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);
Important: 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);
Note: To retrieve indexed data in descending order, use ORDER BY in the SELECT statement to specify retrieval order.

Advance To: