SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-58
Examples of CREATE INDEX
After you perform POPULATE INDEX, you should perform a FUP RELOAD on the
index and all its partitions, to organize the index structure more efficiently and to
reduce index levels.
If the MAXEXTENT value that you specified is too small, when you run the POPULATE
INDEX utility it automatically increases the value to the largest possible size. When
POPULATE INDEX completes it adjusts the MAXEXTENTS value to the value you
specified, if it is greater than the number of extents that needed to be allocated. If the
number of extents that needed to be allocated is greater than the value you specified,
POPULATE INDEX adjusts the value for MAXEXTENTS to a value equal to the
number of extents that it allocated, plus 50. This is similar to NonStop SQL/MP’s
behavior.
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,