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-17
Using MODIFY to Manage Range-Partitioned Tables
and Indexes
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
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 (