SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-22
Splitting, Moving, and Merging Partitions
This example shows how to move the latter portion of one partition into a new partition
(a one-way split), using a define for the index name:
>> ALTER INDEX =XPART_LOC
+> PARTONLY MOVE
+> FROM KEY “H00” UP TO LAST KEY TO =XPART_EUROPE
+> CATALOG =INVENT_EUROPE
+> EXTENT (8,8) SLACK 20;
--- SQL operation complete.
The preceding example uses the FROM KEY value clause together with the UP TO
LAST KEY clause to specify the last part of the partition. (The LAST KEY refers to the
last key value in the partition, not the entire file.) The UP TO LAST KEY clause is
optional; if you omitted it, the preceding example would move the same rows (from
H00 to the last key in the partition) into the new partition.
Performing a Two-Way Partition Split
A two-way split moves all the data from an existing partition into two new partitions.
After a two-way split, SQL automatically drops the original partition. The data is now
divided between the two new, different partitions. You cannot use the WITH SHARED
ACCESS option with a two-way split operation.
You can perform this move to change the format of a partition.
This example shows how to split an existing partition into two new partitions and
register the new partitions in other catalogs (a two-way split):
>>ALTER TABLE $DISK1.SALES.ORDERS
+> PARTONLY MOVE
+> ( FROM FIRST KEY UP TO KEY 50 TO $DISK2 CATALOG =CAT2,
+> FROM KEY 50 UP TO LAST KEY TO $DISK3 CATALOG =CAT3 );
--- SQL operation complete.
The preceding example specifies the two destination volumes with the volume names
only ($DISK2 and $DISK3). You do not have to specify the subvolume and file names
because these names must be the same for each partition in the table or index. (If you
omit the node name, SQL defaults to the local node.)
Moving Partition Boundaries
You can move the boundaries between two existing partitions—that is, move rows from
one partition to another—by using the PARTONLY MOVE option. A move partition
boundary operation moves data in the first or last part of a partition into the logically
adjacent partition. You must use the WITH SHARED ACCESS option to perform this
operation.
During this operation, SQL moves the specified rows and automatically adjusts the key
ranges of the two affected partitions. The move partition boundary operation is similar
to a one-way split, but it moves data into an existing partition instead of a new partition.