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

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—523723-004
9-8
Adding Indexes
Java. For information about using DISPLAY USE OF, see Checking Module
Dependencies With DISPLAY USE OF on page 11-20 and the SQL/MX Reference
Manual.
6. Enter the ALTER TABLE ADD CONSTRAINT statement.
7. Revise the application source code as needed to reflect your changes to the
database. Process and compile the updated source file. For more information, see
the SQL/MX Programming Manual for C and COBOL and the SQL/MX
Programming Manual for Java.
For more information and examples of creating constraints, see Creating Constraints
on SQL/MX Tables on page 7-35.
Adding Indexes
To add an index to an existing table, use the CREATE INDEX statement. To add 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. 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, however.) You should
consider creating indexes on only the most frequently used table columns.
Because CREATE INDEX executes in a single TMF transaction, it could experience
TMF limitations such as a transaction time-out if the created index contains a
particularly large amount of data. CREATE INDEX locks out INSERT, DELETE, and
UPDATE operations on the table being indexed. If other processes have row in the
table locked when the operation begins, CREATE INDEX waits until its lock request is
granted or time-out occurs.
By default, or if you specify the POPULATE option, CREATE INDEX both creates and
loads the new index. If you are creating an index on a large SQL/MX table that is
already populated, use the NO POPULATE option, and then run the POPULATE
INDEX utility to load the index. After running 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 specify is too small, the POPULATE INDEX utility
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 need to be allocated is greater than the value you specify, POPULATE
INDEX adjusts the value for MAXEXTENTS to a value equal to the number of extents
that it allocated, plus 50. This behavior is similar to that of NonStop SQL/MP.
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.