SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-48
Creating Index Partitions
This example creates a unique index on the EMPLOYEE table. Each row must contain
a unique value (a social security number, in this case) because the index is unique.
>> CREATE UNIQUE INDEX IEMPSS ON $VOL1.PERSNL.EMPLOYEE
+> (EMP_SOCIAL_SEC_NUMBER ASC)
+> CATALOG \SYS1.$VOL1.ADMIN;
--- SQL operation complete.
If the index definition specifies multiple columns, the value of the columns as a group
instead of the values of the individual columns determines uniqueness.
You can make all columns unique by creating a unique index for each column of the
base table.
A collation specified for an index column can affect the uniqueness of rows. Rows that
contain unique values in a base table column that uses one collation might not contain
unique values for an index column that uses a different collation. The uniqueness is
based on collation rules.
Creating Index Partitions
The following CREATE INDEX statement creates a partitioned index with the primary
partition and an additional partition. The INFO DEFINE command displays the
DEFINEs used to identify the index, table, and catalog.
-- DEFINEs were previously added during this SQLCI
-- session or inherited from the command interpreter.
>> INFO DEFINE =PCAT;
DEFINE NAME =PCAT
CLASS CATALOG
SUBVOL \SYS1.$VOL1.PERSNL
>> INFO DEFINE =EMPFILE;
DEFINE NAME =EMPFILE
CLASS MAP
FILE $VOL1.PERSNL.EMPLOYEE
>> INFO DEFINE =EMP_NAME_INDEX;
DEFINE NAME =EMP_NAME_INDEX
CLASS MAP
FILE $VOL1.PERSNL.XEMPNAME
>> CREATE INDEX =EMP_NAME_INDEX
+> ON =EMPFILE (LAST_NAME, FIRST_NAME)
+> PARTITION ($VOL2.PERSNL.XEMPNAME
+> CATALOG =PCAT
+> FIRST KEY "M")
+> EXTENT (1000,100)
+> BLOCKSIZE 2048
+> CATALOG =PCAT
+> SECURE "NNOO";
--- SQL operation complete.
Note that an index inherits the partition array value associated with its base table.
For performance considerations see Creating Table Partitions on page 5-32.