SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-151
Examples—CREATE INDEX

This example prevents the addition of duplicate employee names to the employee
table by creating a unique index on the LAST_NAME and FIRST_NAME columns:
CREATE UNIQUE INDEX PERSNL.XEMPNAM
ON PERSNL.EMPLOYEE (LAST_NAME FIRST_NAME)
CATALOG PERSNL;

This example creates a nonpartitioned index using a simple parallel sort with
subsorts. The DEFINEs set up a sort operation with four subsort processes. The
CREATE INDEX statement starts a sort process that uses the specified subsort
processes:
>> ADD DEFINE =_SORT_DEFAULTS, CLASS SORT, SUBSORTS (=SS1,
+> =SS2, =SS3, =SS4);
>> ADD DEFINE =SS1, CLASS SUBSORT, SCRATCH $VOL1;
>> ADD DEFINE =SS2, CLASS SUBSORT, SCRATCH $VOL2;
>> ADD DEFINE =SS3, CLASS SUBSORT, SCRATCH $VOL3;
>> ADD DEFINE =SS4, CLASS SUBSORT, SCRATCH $VOL4;
>> CREATE INDEX AGEINDEX ON CUSTABLE (COL2);

This example creates a Format 2-enabled index with four Format 2 partitions
(assuming that the EMPLOYEE table is a format 2-enabled table):
CREATE INDEX $VOL1.PERSNL.EMPLOYE0 ON
\SYS1.$VOL1.PERSNL.EMPLOYEE (LAST_NAME)
PARTITION ($VOL2.PERSNL.EMPLOYE0 FIRST KEY “D” ,
($VOL3.PERSNL.EMPLOYE0 FIRST KEY “K” ,
($VOL4.PERSNL.EMPLOYE0 FIRST KEY “S” )
FORMAT 2;