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-15
Using MODIFY to Manage Range-Partitioned Tables
and Indexes
MODIFY supports these partition management operations for range partitions:
•
Adding a new empty partition. You can add range partitions online. You can add
one range partition at a time to a hash-partitioned table, but you must do this
offline.
•
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. For NonStop SQL/MX, splitting a single partition into two partitions
requires two steps: splitting off the first half of the partition and then moving the
second half 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.
Examples of Using MODIFY With Range-Partitioned Tables
For these range-partitioned table examples, suppose that the table ORDERSR has
been created in this way:
CREATE TABLE cat2.sch2.ordersr
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
location $DATA01
partition(
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