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-16
Using MODIFY to Manage Range-Partitioned Tables
and Indexes
another disk volume. This MODIFY command splits the $DATA03 partition, moving all
locations from ‘Seattle’ and beyond to a new partition on $DATA04:
MODIFY TABLE cat2.sch2.ordersr add partition
where key = value ('Seattle')
to location $DATA04;
Example of Dropping an Existing Empty Range Partition
Contrary to expectations, no orders have been received from ‘Seattle,’ and no orders
have been received with a location value greater than or equal to ‘Seattle.’ You decide
to get rid of the empty partition that you just created on $DATA04. Use this MODIFY
command to drop that partition:
MODIFY TABLE cat2.sch2.ordersr drop partition
where key = value ('Seattle');
After you issue this command, if new rows with a location value greater than or equal
to ‘Seattle’ arrive, they are inserted into the partition on $DATA03.
Example of Moving an Existing Partition to a New Location
You decide to use $DATA01 for other purposes. You want to move the first partition of
the table to a different volume, $DATA00. Use this MODIFY TABLE command:
MODIFY TABLE cat2.sch2.ordersr move
where key = first partition to location $DATA00;
Example of Splitting an Existing Range Partition and Merging the First or
Last Part to an Existing Partition
Business has improved, and many new orders have been received from ‘Chicago’ and
‘Cincinnati.’ At the same time, very few orders have been received from ‘Atlanta.’ The
result is that the partition on $DATA02 is much more active than the partition on
$DATA00. So you decide to rebalance the load. You do this by moving the key range
from ‘Chicago’ up to—but not including—‘Cincinnati’ to the $DATA00 partition. Use this
MODIFY TABLE command:
MODIFY TABLE cat2.sch2.ordersr move
where key = first key upto key = value ('Cincinnati')
to previous partition;
Example of Merging Two Adjacent Range Partitions Into One Range
Partition
You have sold your New York subsidiary to another company. As a result, ‘New York’
orders soon begin to decline significantly, and there is not enough demand to justify
retaining a separate partition for ‘New York’ orders. You decide to merge the data from