SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-22
Considerations—ALTER INDEX

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. For more information about
the errors returned, see DDL (Data Definition Language) Statements on
page D-20.
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. For more information, see WITH
SHARED ACCESS OPTION on page W-4. 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 a split operation, the move or the split operation cannot
complete until it can obtain an exclusive lock on all partitions. It either waits
until the partition becomes available or times out. If the move or split operation
obtains an exclusive lock then other transactions against the partition time out.
Other partitions of the table are accessible for INSERT, UPDATE, and DELETE
operations, such that processes can make read and write requests for those
partitions. For information about specifying on-demand opens, see the OPEN
ACCESSED PARTITIONS clause under CONTROL TABLE Directive on
page C-77. Without WITH SHARED ACCESS, you might want to stop activity
on a table when you intend to move or split one of the partitions of an index to
the table.
If SQL statements refer to the source partition and the partition is moved, you
might need to change your program or DEFINEs to reference the new location.

Moving, merging, or splitting an index partition invalidates a program that uses
the index, unless the program was compiled with CHECK INOPERABLE
PLANS and the table associated with the index has the SIMILARITY CHECK
option enabled.
A simple move or a split of a partition invalidates previous TMF online dumps
of the affected partition. If you want TMF file-recovery protection, you must
make online dumps of the newly moved or split partitions. (If the operation
specifies the WITH SHARED ACCESS option, you can begin making new
online dumps without waiting for the operation to complete. For more
information, see WITH SHARED ACCESS OPTION on page W-4.)

Merging a partition or moving all or part of a partition into another existing
partition does not change statistics. To update statistics, use the UPDATE
STATISTICS command.