The CREATE INDEX statement creates an index on a table. You can create an index, a unique index, a unique index where not null, or an encoded vector index. Additionally, you can put the index in ascending or descending order by the column and provide an estimate number of distinct key values.
Create an index named UNIQUE_NAM on the PROJECT table. The purpose of the index is to ensure that there are not two entries in the table with the same value for project name (PROJNAME). The index entries are to be in descending order.
CREATE UNIQUE INDEX UNIQUE_NAM ON PROJECT(PROJNAME DESC)
Create an encoded vector index named ENVEIN on the EMPLOYEE table. The purpose of the index is to aid in performance when querying the EMPLOYEE table. The index is assumed to have 256 distinct values.
CREATE ENCODED VECTOR INDEX ENVEIN ON EMPLOYEE (COLUMN DESC) WITH 256 DISTINCT VALUES
Create an index named JOB_BY_DPT on the EMPLOYEE table.
Arrange the index entries in ascending order by
job title (JOB) within each
department (WORKDEPT).
CREATE INDEX JOB_BY_DPT ON EMPLOYEE (WORKDEPT, JOB)
For more information, see CREATE INDEX in the SQL Reference topic in the
Information Center.