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-18
Using MODIFY to Manage Hash-Partitioned Tables
and Indexes
MERGE operations. The only table operation that cannot be performed on an index is
REUSE. The next examples illustrate these supported operations.
Example of Adding a New Range Partition
Business has exceeded expectations in the first half of 2004. The index partition on
$DATA02 has become too large, and you want to move May and June orders to a new
partition, $DATA04. Use this MODIFY command:
MODIFY INDEX cat2.sch2.ordersr_via_ordertime
add partition
where key =
value (timestamp '2004-05-01 00:00:00.000000')
to location $data04;
Example of Dropping an Existing Empty Range Partition
Suppose that your company has archived all the orders from 2003 and earlier and no
longer needs to keep them in the ORDERSR table. You have deleted all records with
ORDERTIMEs in 2003 and earlier from the table. As a result, the index partition
$DATA01 is empty. Use this MODIFY command to drop this empty index partition:
MODIFY INDEX cat2.sch2.ordersr_via_ordertime
drop partition
where first partition;
In this example, you use the syntax first partition instead of specifying a key
value to refer to the first index partition.
Example of Moving an Existing Partition to a New Location
You suddenly need to use $DATA04 for other purposes. Use this MODIFY command
to move the index partition on $DATA04 to another volume:
MODIFY INDEX cat2.sch2.ordersr_via_ordertime
move
where key =
value (timestamp '2004-05-01 00:00:00.000000')
to location $data05;
Using MODIFY to Manage Hash-Partitioned Tables and Indexes
Use MODIFY to manage hash partitions of SQL/MX tables and indexes. You can drop
only the last partition. You must manage tables and indexes separately, regardless of
whether or not they are related.
Note. In the MODIFY INDEX command, the catalog and schema names of the index follow
the usual default naming rules. In particular, if the catalog or schema name is not specified, the
default catalog or schema name from the session is used. In the CREATE INDEX command,
however, the catalog and schema names cannot be specified for the index name. Instead, the
CREATE INDEX command always creates the index in the same catalog and schema as the
underlying base table
.