SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-21
Splitting, Moving, and Merging Partitions
Performing a One-Way Partition Split
A one-way split moves data in the first or last part of a partition into a new partition. The
remaining part of the data stays in the original partition. You can perform a one-way
split with or without using the WITH SHARED ACCESS option.
You can perform this move to change the format of a partition.
If you do not use the WITH SHARED ACCESS option, you can only move data in the
last part of the partition to a new partition; you cannot move data in the first part of the
partition. That is, you can only use the FROM KEY value clause with the PARTONLY
MOVE option; you cannot use the UP TO KEY value clause. (You can optionally use
the UP TO LAST KEY clause, which indicates that SQL should move the data from the
FROM KEY value up to the last key value in the partition.)
If you do use the WITH SHARED ACCESS option, you can specify either the first or
last part of the data during the operation. That is, you can use either the FROM KEY
value clause or the UP TO KEY value clause with the PARTONLY MOVE option.
This example splits an existing partition of an index. Before the split operation, the
index has three partitions with these starting key values:
In this one-way split operation, the starting key value for the new index partition is
5000. The new partition resides on $VOL4. Rows with index key values that equal or
exceed 5000, but that are less than the starting key value assigned to the next numeric
partition (that is, rows with key values from 5000 to 9999) are relocated to the new
partition:
>> ALTER INDEX $VOL1.SALES.CUSTNAME
+> PARTONLY MOVE FROM KEY 5000 TO $VOL4.SALES.CUSTNAME
+> CATALOG $VOL1.SALES
+> EXTENT (1000,200);
--- SQL operation complete.
After the split operation, the index has four partitions with these starting key values:
Partition Location Starting Key Value
$VOL1 0
$VOL2 10000
$VOL3 20000
Partition Location Starting Key Value
$VOL1 0
$VOL4 5000
$VOL2 10000
$VOL3 20000