NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-21
Considerations—ALTER INDEX
specify only a node name and 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, specified as dest-part, must be unique for each
partition of the same object.
The two-way split does not support the WITH SHARED ACCESS option.
°
A one-way move operation moves the first or last part of a partition to its
logically adjacent partition, leaving the other part in the existing partition. This
operation essentially moves the boundary of the partition; for example:
MOVE UP TO KEY val TO dest-part WITH SHARED ACCESS
MOVE FROM KEY val TO dest-part WITH SHARED ACCESS
A one-way move is similar to a one-way split, but moves data to an existing
partition instead of a new partition.
In a one-way move, name specifies a valid partition of the index. NonStop
SQL/MP determines the actual source partition during execution. The WITH
SHARED ACCESS option is required.
After a successful one-way move operation, run FUP RELOAD to reclaim
unused disk space. For more information about FUP RELOAD, see the File
Utility Program (FUP) Reference Manual.
•
Reconfiguring partitions of indexes
°
Access requirements
All partitions of the index must be accessible when you add a new partition to
an index. ALTER INDEX returns an error if you attempt to add a partition while
another process has a partition locked or while another process is attempting to
execute a DDL operation on the same partition. See DDL (Data Definition
Language) Statements on page D-19 for more information about the errors
returned.
If you specify WITH SHARED ACCESS, SQL allows concurrent INSERT,
UPDATE, DELETE and read-only utility operations on a partition being moved
throughout most of the move operation. (See WITH SHARED ACCESS
OPTION on page W-4 for details.) You can use the COMMIT option to control
when the commit phase occurs and to specify the timeout period for lock
requests and the handling of retryable errors (such as errors in lock requests)
during the commit phase of the ALTER INDEX operation.
Without WITH SHARED ACCESS, a partition being moved or split is not
accessible until the ALTER INDEX operation finishes.
If the partition is being accessed for a SELECT or read operation concurrent
with a move or split operation, the move or split operation cannot complete until
it can obtain an exclusive lock on all partitions, and so will either wait until the
partition becomes available or time out. If the move or split operation obtains an
exclusive lock, other transactions against the partition might time out.
Other partitions of the table are accessible for INSERT, UPDATE, and DELETE
operations, so processes can make read and write requests for those partitions.