SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-60
Examples of CREATE INDEX
Authorization and Availability Requirements
To create an SQL/MX index, you must own the schema for the underlying table or be
the super ID, and have access to all partitions of the underlying table.
CREATE INDEX locks out INSERT, DELETE, and UPDATE operations on the table
being indexed. If other processes have rows in the table locked when the operation
begins, CREATE INDEX waits until its lock request is granted or timeout occurs.
An index always has the same security as the table it indexes, so users authorized to
access the table can also access the index. You cannot access an index directly.
Limits on Indexes
For nonunique indexes, the sum of the lengths of the columns in the index plus the
sum of the length of the clustering key of the underlying table cannot exceed 255
bytes. For unique indexes, the sum of the lengths of the columns in the index cannot
exceed 255 bytes.
There is no restriction on the number of indexes per table.
There is no restriction on the number of partitions an index supports.
Examples of CREATE INDEX
•
This example creates an 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 $data2,
ADD FIRST KEY 'T' LOCATION $data3);