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

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—523723-004
10-19
Using MODIFY to Manage Hash-Partitioned Tables
and Indexes
You can perform only offline partition management operations for hash-partitioned
tables and indexes. 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 the last existing hash partition and rebalancing data.
•
Moving an existing hash partition to a new location.
Examples of Using MODIFY With Hash-Partitioned Tables
For these hash-partitioned index examples, suppose that the table ORDERSH has
been created in this way:
CREATE TABLE cat3.sch3.ordersh
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
location $DATA01
hash partition(
add location $DATA02,
add location $DATA03);
Example of Adding a New Hash Partition and Rebalancing Data
The table ORDERSH is partitioned three ways. When business orders increase, you
decide to rebalance the orders data over four disks. Use this MODIFY TABLE
command to add the fourth partition:
MODIFY TABLE cat3.sch3.ordersh add partition
location $DATA04;
In response to this command, a portion of the data in each of the original three
partitions is moved automatically to the fourth partition, rebalancing the data evenly
across the four partitions.
Example of Dropping an Existing Hash Partition and Rebalancing Data
Orders suddenly decline, and you decide to free up some of the disk resources used
by the table ORDERSH. Use this MODIFY TABLE command to drop the last partition
and redistribute its data to the remaining partitions:
MODIFY TABLE cat3.sch3.ordersh drop partition
where location $DATA04;