Syntax CREATE [UNIQUE] INDEX index-Name
ON table-Name ( Simple-column-Name [ ASC | DESC ]
[ , Simple-column-Name [ ASC | DESC ]] * )The
maximum number of columns for an index key in is
16.
An index name cannot exceed 128 characters.
A column must not
be named more than once in a single CREATE INDEX statement. Different indexes
can name the same column, however.
can
use indexes to improve the performance of data manipulation statements (see ). In addition, UNIQUE
indexes provide a form of data integrity checking.
Index names are
unique within a schema. (Some database systems allow different tables in a
single schema to have indexes of the same name, but does
not.) Both index and table are assumed to be in the same schema if a schema
name is specified for one of the names, but not the other. If schema names
are specified for both index and table, an exception will be thrown if the
schema names are not the same. If no schema name is specified for either table
or index, the current schema is used.
Descending indexesIndexescreated
in descending orderBy default, uses
the ascending order of each column to create the index. Specifying ASC after
the column name does not alter the default behavior. The DESC keyword after
the column name causes to
use descending order for the column to create the index. Using the descending
order for a column can help improve the performance of queries that require
the results in mixed sort order or descending order and for queries that select
the minimum or maximum value of an indexed column.
Indexesspecifying
page size for
If a qualified index name is
specified, the schema name cannot begin with SYS.
Indexes and constraints Backing
indexescreated automatically to enforce primary key, unique, and
foreign key constraintsIndexesautomatic
creation of for primary key, unique, and foreign key constraintsUnique,
primary key, and foreign key constraints generate indexes that enforce or
"back" the constraint (and are thus sometimes called backing indexes).
If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it,
you can not create an index on those columns. has
already created it for you with a system-generated name. System-generated
names for indexes that back up constraints are easy to find by querying the
system tables if you name your constraint. For example, to find out the name
of the index that backs a constraint called FLIGHTS_PK:
SELECT CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES,
SYS.SYSCONSTRAINTS WHERE
SYS.SYSCONGLOMERATES.TABLEID = SYSCONSTRAINTS.TABLEID
AND CONSTRAINTNAME = 'FLIGHTS_PK'