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

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—544536-007
9-11
Adding Partitions to SQL/MX Tables and Indexes
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-33.
Adding Partitions to SQL/MX 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 and Managing Partitions for SQL/MX Tables on page 7-16
•
Restrictions on Creating and Placing Partitions on page 7-17
•
The 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.
•
A certain subset of a remote table’s data is accessed more frequently at the local
node than from a remote node, partitioning the table so that the frequently
accessed portion of the data resides on the local node can increase local
performance.
•
Queries are processed in parallel, partitioning a table or index is often required.
Partitioning is necessary, for example, for parallel execution of a SELECT
statement on a single table. Even for join queries, which do not require partitioning
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.