SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

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” (page 188).
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;
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 index.
Use this MODIFY command to drop the fourth partition and redistribute its data to the remaining
partitions:
MODIFY INDEX cat2.sch2.ordersr_via_ordernumber
drop partition
where last partition;
192 Reorganizing SQL/MX Tables and Maintaining Data