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-17
Using MODIFY to Manage Range-Partitioned Tables
and Indexes
this partition into the partition on $DATA02 and to drop the $DATA03 partition. Use this
MODIFY TABLE command:
MODIFY TABLE cat2.sch2.ordersr move
where key = value ('New York') thru key = last key
to previous partition;
Note that the DROP PARTITON syntax is not used in this example because the
partition $DATA03, which contains ‘New York,’ is not yet empty.
Example of Reusing an Existing Range Partition by Setting the FIRST KEY
Values to New Values
You have reorganized your business so that all orders that originate in ‘Chicago’ and
all locations with values greater than ‘Chicago’ are processed through an agent in
‘Boston.’ You have already deleted all rows from the ORDERSR table with locations
greater than or equal to the value of ‘Chicago,’ which empties the ‘Cincinnati’ partition,
$DATA02. All new orders should go into the $DATA00 partition. Consequently, you
decide to drop the ‘Cincinnati’ partition and create a new partition for ‘Boston’ orders.
Use this MODIFY REUSE command to do this in one operation:
MODIFY TABLE cat2.sch2.ordersr reuse partition
where key = value ('Cincinnati')
with key = value ('Boston');
Examples of Using MODIFY With Range-Partitioned Indexes
These range-partitioned index examples are based on the range-partitioned table
ORDERSR from Examples of Using MODIFY With Range-Partitioned Tables on
page 10-15. Suppose that it has become necessary to group customer orders based
on the time they were placed. Although you can perform grouping against the base
table directly, suppose that there is a requirement for direct access to particular dates.
One way to perform the grouping is to add this range-partitioned index on the
ORDERTIME column of ORDERSR:
CREATE INDEX ordersr_via_ordertime
ON cat2.sch2.ordersr (ordertime)
LOCATION $data01
PARTITION (
ADD FIRST KEY TIMESTAMP '2004-01-01 00:00:00.000000'
LOCATION $data02,
ADD FIRST KEY TIMESTAMP '2004-07-01 00:00:00.000000'
LOCATION $data03 );
The partition on $DATA01 contains index records for orders from 2003 and earlier, the
partition on $DATA02 contains index records for orders from the first half of 2004, and
the partition on $DATA03 contains index records for orders for the second half of 2004
and later.
Managing partitions on a range-partitioned index is quite similar to managing partitions
on a range-partitioned table. You can still perform ADD, DROP, MOVE, SPLIT, and