SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-78
Examples of CREATE INDEX
data is to be moved. For more information about creating and populating indexes, see
the SQL/MX Installation and Management Guide.
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 or object owner for the underlying table, 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 2010
bytes for 4K blocks and 2048 bytes for 32K blocks. For unique indexes, the sum of the
lengths of the columns in the index cannot exceed 2010 bytes for 4K blocks and 2048
bytes for 32K blocks.
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);