SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-34
Partitioning Indexes
Use the MODIFY INDEX utility to manage range-partitioned and hash-partitioned
indexes. For detailed information and examples, see the information on using MODIFY
to manage table and index partitions in Section 10, Reorganizing SQL/MX Tables and
Maintaining Data.
An important consideration when you partition indexes is whether to co-locate the
index partitions with the base table partitions. For more information on co-location, see
the information on using co-located indexes in Section 16, Enhancing SQLMX
Database Performance.
Examples for Creating a Range-Partitioned Index
This example creates a range-partitioned index on two columns of a table:
CREATE INDEX xempname
ON persnl.employee (last_name, first_name);
This example creates and partitions a unique index (one that could be used to support
a UNIQUE, PRIMARY KEY, or referential constraint) on a table:
CREATE UNIQUE INDEX XEMP
ON persnl.employee (LAST_NAME, EMPNUM)
LOCATION $data1
ATTRIBUTE NO AUDITCOMPRESS
PARTITION (ADD FIRST KEY 'E' LOCATION $data1,
ADD FIRST KEY 'J' LOCATION $data2,
ADD FIRST KEY 'O' LOCATION $data3,
ADD FIRST KEY 'T' LOCATION $data3);
Examples for Creating a Hash-Partitioned Index
This example creates a hash-partitioned index on two columns of a table:
CREATE INDEX xempname
ON persnl.employee (last_name, first_name)
HASH PARTITION;
This example creates and hash-partitions a unique index (one that could be used to
support a UNIQUE, PRIMARY KEY, or referential constraint) on a table. The
partitioning key consists of the index columns LAST_NAME and EMPNUM:
CREATE UNIQUE INDEX XEMP
ON persnl.employee (LAST_NAME, EMPNUM)
LOCATION $data1
ATTRIBUTE NO AUDITCOMPRESS
HASH PARTITION
(ADD LOCATION $data2,
ADD LOCATION $data3,
ADD LOCATION $data4,
ADD LOCATION $data5);