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-20
Using MODIFY to Manage Hash-Partitioned Tables
and Indexes
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.
Example of Adding a New Hash Partition and Rebalancing Data
The index ORDERSR_VIA_ORDERTIME contains three partitions. When business
orders increase, you decide to rebalance the index over four partitions. Use this
MODIFY command to add the fourth partition:
MODIFY INDEX cat2.sch2.ordersr_via_ordernumber
add partition location $data04;