SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Utilities
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
5-90
Considerations for MODIFY
•
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.
Renaming Guardian locations of partitions of tables and
indexes
You can use the rename option to rename Guardian locations of partitions of a table or
an index. The following are the prerequisites for a successful rename:
•
The table or index and all the partitions must be available.
•
All specified locations must belong to the table or index. If the pattern form of
rename-spec is used, the pattern must match at least one of the partitions of the
table or index.
•
The target Guardian locations must not already exist.
•
For all partitions of the target object, file labels must be available.
•
For a Guardian rename of an index, file labels for all partitions of base table of the
index must be available.
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
To specify the number of rows to be copied in a transaction, use the CONTROL
QUERY DEFAULT statement or insert an entry to the SYSTEM_DEFAULTS table. For
offline partition operations, use the PM_OFFLINE_TRANSACTION_GRANULARITY
attribute. For online partition operations, use the
PM_ONLINE_TRANSACTION_GRANULARITY attribute.