SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-144
CREATE INDEX Statement
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 SMF is installed on your node, the PHYSVOL option directs SQL to override
SMF and place the index or primary partition on the physical volume
volume-name. For volume-name, specify either a physical volume or equivalent
DEFINE. Do not include the node name in your volume name.
You can specify a physical volume for each secondary partition in the PARTITION
clause.
This option is available only if you specify a virtual volume for index.
volume-name must belong to the virtual volume you specify.
INVALIDATE | NO INVALIDATE
specifies whether to invalidate programs that use the underlying table,:
If you do not specify either INVALIDATE or NO INVALIDATE and the underlying
table has similarity checks enabled, SQL handles programs compiled with CHECK
INOPERABLE plans as if you specified NO INVALIDATE and handles other
programs as if you specified INVALIDATE.
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
INVALIDATE Invalidate all programs that use the underlying table and
modify the table's redefinition timestamp
NO INVALIDATE Do not invalidate programs or modify the table's redefinition
timestamp