CREATE INDEX
Go Up to Statement and Function Reference (Language Reference Guide)
Creates an index on one or more columns in a table. Available in gpre, DSQL, and isql.
By default indices reside in the same tablespace as that of the table unless a different tablespace is specified. For more information on table spaces refer to the Tablespace documentation.
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX <index>
ON <table> (<col> [, <col> …]);
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in
isql, the semicolon is a terminating symbol for the statement, so it must be included.| Argument | Description |
|---|---|
|
|
Prevents insertion or updating of duplicate values into indexed columns. |
|
|
Sorts columns in ascending order, the default order if none is specified. |
|
|
Sorts columns in descending order. |
|
<index> |
Unique name for the index. |
|
<table> |
Name of the table on which the index is defined. |
|
<col> |
Column in <table> to index. |
Description: Creates an index on one or more columns in a table. Use CREATE INDEX to improve the speed of data access. Using an index for columns that appear in a WHERE clause may improve search performance.
You cannot index Blob columns or arrays.
A UNIQUE index cannot be created on a column or set of columns that already contains duplicate or NULL values.
ASC and DESC specify the order in which an index is sorted. For faster response to queries that require sorted values, use the index order that matches the s ORDER BY clause of the query. Both an ASC and a DESC index can be created on the same column or set of columns to access data in different orders.
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.
Examples: The following isql statement creates a unique index:
CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);
The next isql statement creates a descending index:
CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY (CHANGE_DATE);
The following isql statement creates a two-column index:
CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);
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 Expression Index