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

SQL/MX Utilities
HP NonStop SQL/MX Reference Manual—540440-003
5-59
Considerations for MODIFY
Offline Partition Management for Range Partitions
MODIFY supports these offline partition management operations for range partitions:
•
Adding a new empty partition.
•
Dropping an existing empty partition.
•
Moving an existing partition to a new location.
•
Splitting an existing partition and then moving the first or last part of the data to a
new partition.
•
Splitting an existing partition and then merging the first or last part of the data to an
existing adjacent partition.
•
Merging two adjacent partitions into one.
•
Reusing an existing partition by setting the FIRST KEY values of the partition to
new values. You can optionally remove existing data in the partition to be reused.
Offline Partition Management for Hash Partitions
MODIFY supports these offline partition management operations for hash partitions:
•
Adding a new hash partition and rebalancing data (that is, redistributing existing
data to all partitions, including the new partition)
•
Dropping an existing hash partition and rebalancing data
•
Moving an existing hash partition to a new location
Offline Partition Management for System-Clustered
Partitions
MODIFY supports moving an entire system-clustered partition to a new location.
MODIFY and Indexes
If there are no indexes on a table, the reuse form of MODIFY purges data from the
partition. If there are existing indexes, MODIFY performs a DELETE operation to
remove the index data, which can take some time to complete.
MODIFY and TMF
Many partition management requests require movement of massive amounts of data.
Because these operations might take longer than the set TMF time limit whose default
is two hours, operations involving data movement are performed in multiple
transactions.
Specifying the Number of Rows per Transaction