NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-22
Considerations—ALTER INDEX
(See the OPEN ACCESSED PARTITIONS clause under CONTROL TABLE
Directive on page C-72 for information about specifying on-demand opens.)
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.
°
Effect on dependent objects and online dumps
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. See WITH
SHARED ACCESS OPTION on page W-4 for details.)
°
Effect on statistics
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.
°
Performance considerations
ALTER INDEX operations that use WITH SHARED ACCESS generally take
longer to complete than those that do not. However, because WITH SHARED
ACCESS operations allow concurrent read and write access to the source
partition, they cause far less application downtime than equivalent operations
without WITH SHARED ACCESS.
The duration of a WITH SHARED ACCESS operation increases with the
number and length of transactions on the node that contains the source partition,
particularly with the number and length of transactions that involve the source
partition and the amount of activity on the audit trail used for the source
partition.
°
TMF audit trail requirements
An operation that uses WITH SHARED ACCESS cannot complete successfully
unless the TMF audit trail generated during the operation is accessible for
reading later in the operation. If a required audit trail has been overwritten, a
WITH SHARED ACCESS operation cancels changes made to the database and
terminates.
When performed on a source object that has a valid TMF online dump, an
operation that uses WITH SHARED ACCESS generates audit information for
the target object.