NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-135
CREATE INDEX Statement
index can reside on any node or volume, independent of the location of the
underlying table, but the volume on which the index is created must be audited by
the TMF subsystem, even if the index itself is nonaudited. (An index is nonaudited
if its underlying table is nonaudited.)
table
is the name of the table for which to create the index (or an equivalent DEFINE).
col [ ASC[ENDING] | DESC[ENDING] ] [ collate-spec ]
specifies a column to include in the index, the order in which to store and retrieve
key values in the column within the index, and a collating sequence for the column
within the index.
The number of columns allowed in an index depends on the length of the index key.
See Index Keys
on page I-9 for more information on limitations.
col must be a column in table, but does not need to be adjacent to other columns
specified for the index or in the same order relative to other columns as in the table.
ASCENDING is the default order for col.
COLLATE { collation | CHARACTER SET }
specifies an alternate collating sequence for the column within the index. You can
use this clause only if the associated column is of a data type that allows a collating
sequence as part of its definition.
Specifying a collation for the index might affect the performance of certain
queries using the index because SQL cannot perform hash joins or hash groupings
on indexes with collations.
The default collating sequence for the column within the index is the same as the
collating sequence for the corresponding column of the underlying table.
CATALOG catalog
specifies the name of the catalog in which to describe the index (or an equivalent
DEFINE). catalog is the name of the subvolume that contains the catalog. The
index and catalog must be on the same node. The default is the current default
catalog.
PHYSVOL volume-name
If ServerWare SMF is installed on your node, the PHYSVOL option directs SQL to
override ServerWare SMF and place the index or primary partition on the physical
collation is the name of an existing collation (or an equivalent DEFINE)
that specifies a collating sequence and uses the same character set
as the associated column
CHARACTER SET specifies a collating sequence based on the binary value of
characters in the column