SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-20
Considerations—ALTER INDEX

MOVE can perform a simple move, a merge (into an existing partition), a one-way
or two-way split (to new partitions) or a one-way move (to an existing partition).
Note that an index or an index partition cannot be open, even for read access,
during an ALTER INDEX MOVE operation, or else the operation fails.

A simple move moves the partition to another volume:
MOVE TO dest-part [WITH SHARED ACCESS]
name specifies the partition being moved. You can specify a simple move with
or without the WITH SHARED ACCESS option.

A merge operation moves the partition into another existing partition, deleting
the original partition:
MOVE TO dest-part WITH SHARED ACCESS
name specifies the actual partition being moved. The WITH SHARED ACCESS
option is required.

A one-way split moves the first or last part of a partition to a new partition,
leaving the remaining part in the existing partition:
MOVE FROM KEY val TO dest-part [WITH SHARED ACCESS]
MOVE UP TO KEY val TO dest-part WITH SHARED ACCESS
In a one-way split, name specifies a partition of the index. The partition that is
split is the one whose data range would include the key val, even if the index
partition does not actually contain a row with that key.
For a one-way split operation, the subvolume name and the simple file name
for the new partition (whether specified explicitly or by default) must be
identical to the subvolume name and the simple file name for every other
partition of the same object. ALTER INDEX uses those names if you specify
only a node name and a volume name or specify only a volume name (which
causes the node to default to the local node). The combination of node name
and volume name must be unique for each partition of the same object.
The first part can be moved only if you include the WITH SHARED ACCESS
option.
A one-way split without the WITH SHARED ACCESS option requires additional
space on the disk that contains the partition being split while the split is in
progress. The amount of additional space required can be as much as the size
(EOF) of the original partition. A one-way split also requires space within the
file itself.
If you are splitting a partition because the disk is full (error 43), it might be
permissible to use a two-way split. If you choose to perform a one-way split,
use the WITH SHARED ACCESS option. Run a DSAP report to show the
largest number of pages of free space. After you successfully perform the
one-way split operation, run FUP RELOAD to reclaim unused space in the file.
For more information on FUP RELOAD, see the File Utility Program (FUP)