SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-23
Splitting, Moving, and Merging Partitions
Suppose, for example, that a partition residing on $DISK2 contains rows in the key
range 3000 through 5999, and a partition residing on $DISK3 contains the key range
6000 through 8999. Suppose further that users have inserted many more rows into
lower key range than the higher one, and that you want to adjust the partition
boundaries accordingly.
This example moves rows from the partition residing on $DISK2 into the adjacent
partition residing on $DISK3. SQL moves the rows starting with the value 5000 up to
the last row in the partition.
>>ALTER TABLE $DISK2.SALES.ORDERS
+> PARTONLY MOVE
+> FROM KEY 5000 UP TO LAST KEY TO $DISK3
+> CATALOG $DISK1.SALES
+> WITH SHARED ACCESS;
--- SQL operation complete.
The preceding example specifies the table by naming the exact partition from which
rows will be moved (ALTER TABLE $DISK2.SALES.ORDERS). In fact, you can specify
any partition to identify the table; SQL will move rows from the correct partition based
on the FROM KEY value clause you specify.
Merging Partitions
A merge operation moves all the data in a partition into an existing, logically adjacent
partition. Merging partitions is a form of moving partition boundaries, except that the
merge operation moves all rows from the original partition into the destination partition.
After a merge operation, SQL automatically drops the original partition.
A merge operation is also similar to a move operation, except that it moves the data
into an existing partition instead of a new partition. You must use the WITH SHARED
ACCESS option with a merge operation.
This example moves all rows from the partition of the CUSTOMER table residing on
$VOL10 into the existing partition residing on $VOL11. The two partitions are logically
adjacent:
>>ALTER TABLE $VOL10.SALES.CUSTOMER
+> PARTONLY MOVE TO $VOL11
+> CATALOG $VOL1.SALES
+> WITH SHARED ACCESS;
--- SQL operation complete.
To perform a merge operation, you must specify the exact partition being merged in the
ALTER TABLE statement. The merge operation does not require you to specify a key
range because, like a move operation, it moves all the rows in the partition. In the
preceding example, suppose that he FIRST KEY value of the partition residing on
$VOL10 is 5000, and the FIRST KEY value of the partition on $VOL11 is 10000. After
the merge operation, the FIRST KEY of the partition residing on $VOL11 is 5000, and
the partition on $VOL10 no longer exists.