NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-40
Considerations—ALTER TABLE
successfully acquire the locks while other transactions are active and cause
those transactions to time out.
If SQL statements refer to the source partition and the partition is moved with a
simple move operation, you might need to change your program or DEFINEs to
reference the new location.
°
Effect on dependent objects and online dumps
If you add, move, or split a partition to a table that has a protection view defined
on it, SQL automatically creates a corresponding partition for the protection
view on the same subvolume as the table partition and writes the view
description in the same catalog as the table partition description.
Moving, merging, or splitting a table partition invalidates a program that uses
the table or a dependent view, unless the program was compiled with CHECK
INOPERABLE PLANS and the table or view 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, comments, and help text
Merging a partition or moving all or part of a partition into another existing
partition does not change related items such as statistics, comments, and help
text. To update statistics, use the UPDATE STATISTICS command. Check your
comments and help text to make sure they still apply.
°
General requirements for partitions
The first key value of a new partition cannot duplicate the first key value of
another partition of the table.
You cannot create a partition on a nonaudited volume, even if the table that
includes the partition is a nonaudited table.
New partitions must comply with the limits on the number and size of partitions.
See Limits
on page L-5 for more information.
You can partition tables of any file organization, but you cannot partition a key-
sequenced table that has a system-defined primary key (as opposed to a user-
defined primary key) unless it also has a clustering key.
°
Performance considerations
ALTER TABLE 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.