Creating Indexes
An index is based on one or more columns in a table. It orders the contents of the specified columns and stores that information on disk in order to speed up access to those columns. Although they improve the performance of data retrievals, indexes also take up disk space and can slow inserts and updates, so they are typically used on frequently queried columns. Indexes can also enforce uniqueness and referential integrity constraints.
InterBase automatically generates indexes on UNIQUE and PRIMARY KEY columns. See the Data Definition Guide for more information about constraints.
You use the CREATE INDEX statement to create an index. The simplified syntax is as follows:
CREATE INDEX name ON table (columns)
Optionally, you can add one or more of the ASCENDING, DESCENDING, or UNIQUE keywords following the CREATE INDEX keywords.
Creating the Namex Index
Define an index for the Employee table, by entering the following code:
CREATE INDEX namex ON Employee (last_name, first_name)
This statement defines an index called namex for the last_name and first_name columns in the Employee table.
Preventing Duplicate Index Entries
To define an index that eliminates duplicate entries, include the UNIQUE keyword in CREATE INDEX. After a UNIQUE index is defined, users cannot insert or update values in indexed columns if the same values already exist there.
For unique indexes defined on multiple columns, such as prodtypex in the example below, the same value can be entered within individual columns, but the combination of values entered in all columns of the index must be unique for each row.
You cannot create a UNIQUE index on columns that already contain non-unique values.
Creating a UNIQUE Index
Create a unique index named prodtypex, on the Project table by entering the following:
CREATE UNIQUE INDEX prodtypex ON Project (product, proj_name)