SQL/MX 2.x Installation and Management Guide (H06.04+)

Reorganizing SQL/MX Tables and Maintaining Data
HP NonStop SQL/MX Installation and Management Guide—540436-001
10-16
Using MODIFY to Manage Range-Partitioned Tables
and Indexes
add first key ('Chicago') location $DATA02,
add first key ('New York') location $DATA03 );
Also suppose that the table ORDERSR contains orders with these locations:
•
Atlanta
•
Chicago
•
Cincinnati
•
New York
Example of Adding a New Range Partition
You expect several thousand orders for ‘Seattle’ to arrive shortly. As the table
ORDERSR is currently defined, you would insert these orders into the partition on
$DATA03. But if $DATA03 is already quite active, you might elect to split this load over
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