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-20
Using MODIFY to Manage Hash-Partitioned Tables
and Indexes
In response to this command, a portion of the data in each of the original three
partitions is moved automatically to the fourth partition, rebalancing the data evenly
across the four partitions.
Example of Dropping an Existing Hash Partition and Rebalancing Data
Orders suddenly decline, and you decide to free up some of the disk resources used
by the table ORDERSH. Use this MODIFY TABLE command to drop the last partition
and redistribute its data to the remaining partitions:
MODIFY TABLE cat3.sch3.ordersh drop partition
where location $DATA04;
Example of Moving an Existing Hash Partition to a New Location
You decide to use $DATA03 for another purpose and to move the hash partition on
$DATA03 to a different volume. Use this MODIFY TABLE command to move the
partition to a new location:
MODIFY TABLE cat3.sch3.ordersh move partition
where location $data03
to location $DATA05;
Examples of Using MODIFY With Hash-Partitioned Indexes
Because index partitioning is independent of base table partitioning, you are not
restricted to using the same partitioning scheme for the index as for the base table. For
example, you can create a hash-partitioned index on a range-partitioned table and
create a range-partitioned index on a hash-partitioned table.
The examples in this subsection are based on the range-partitioned table ORDERSR
from Examples of Using MODIFY With Range-Partitioned Tables on page 10-15.
Suppose that it becomes necessary to access orders directly from the table column
ORDERNUMBER. You decide to create an index on this column. You are not
concerned about accessing ranges of ORDERNUMBER; it is enough to access a
particular ORDERNUMBER row directly. You also want to keep the partitions of your
index evenly balanced. Therefore, you create this hash-partitioned index:
CREATE INDEX ordersr_via_ordernumber
ON cat2.sch2.ordersr (ordernumber)
LOCATION $data01
HASH PARTITION (
ADD LOCATION $data02,
ADD LOCATION $data03);
The index records are hashed on ORDERNUMBER and distributed among the index
partitions $DATA01, $DATA02, and $DATA03.
You use MODIFY to manage a hash-partitioned index much as you would to manage
partitions on a hash-partitioned table. You can perform ADD, DROP, and MOVE
operations, as illustrated in these examples.