The CREATE INDEX statement creates an index for a table based on the given columns.
The keyword UNIQUE specifies that the column(s) being indexed must contain unique values. If more than one column is specified, the combination of columns must have a unique value, but the individual columns do not need to have unique values.
For example, if you create an index on the combination of LAST_NAME and FIRST_NAME, the following data values are acceptable because although there are duplicate first names and duplicate last names, no 2 rows have the same value for both first name and last name.
SMITH, PATTI SMITH, DAVID JONES, DAVID
Keywords ASC and DESC specify whether the given columns should be indexed in ascending or descending order. If neither ASC nor DESC is specified, ascending order is used.
If the index_name starts with a digit, the index_name must be given in double quotation marks.
Examples
CREATE UNIQUE INDEX UX_TEST ON TEST (I); CREATE INDEX X_TEST ON TEST (I DESC, J DESC); CREATE INDEX "053_TEST" ON TEST (I DESC, J DESC);