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-11
Adding Partitions to Tables and Indexes
7. Enter an UPDATE STATISTICS statement to update the statistical information
stored in the catalog and get statistics on the new index.
8. 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.
9. Make a new TMF online dump containing the new index.
For more information and examples of adding an index, see Precreating Indexes or
Managing Constraint-Created Indexes on page 4-13 and Creating Indexes for SQL/MX
Tables on page 7-31.
Adding Partitions to Tables and Indexes
If an index or a table is stored by a user-specified primary key or a key column list, you
can add partitions to the index or table by using the MODIFY utility. MODIFY is a
syntax-based utility that can be executed through MXCI, and enables database
administrators to perform partition operations on range and hash partitions of SQL/MX
tables and indexes. Depending on the type of operation you are performing, MODIFY
can be run as an online or offline operation.
For more information about creating and using table and index partitions, see:
•
Planning Table and Index Partitioning on page 4-8
•
Range Partitioning and Hash Partitioning on page 4-8
•
Creating Table Partitions on page 7-15
•
Guidelines for Creating and Placing Your Partitions on page 7-17
•
SQL/MX Reference Manual
Evaluating the Benefit of a New Partition
Partitioning can provide significant benefits to a wide variety of applications by
improving manageability, performance, and availability. It is not unusual for partitioning
to improve the performance of certain queries or maintenance operations by an order
of magnitude.
Partitioning might increase performance if:
•
Disk accesses are queued in the disk process, partitioning the table across
multiple volumes might increase performance.
•
A partition of a table or index is full, the partition can be split into two partitions.
Caution. Except for the operation to drop a range partition, partition operations are done in
multiple transactions. If the partition operation fails, you might need to use the RECOVER
utility to cancel or resume the failed partition operation.