NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-18
Considerations—ALTER INDEX
For more detail about ALLOCATE, MAXEXTENTS, or RESETBROKEN, see
ALLOCATE File Attribute on page A-6, MAXEXTENTS File Attribute on
page M-2, or RESETBROKEN File Attribute on page R-22. For more information
about INCOMPLETE SQLDDL OPERATION, see Completing ALTER INDEX
Operations in Considerations—ALTER INDEX
on page A-18.
ADD PARTITION new-part add-spec
adds a partition named new-part to index name using the options specified in
add-spec. When specifying new-part, include the volume, subvolume, and file
name of the partition.
If ServerWare SMF is installed on your node, the volume can be a virtual or direct
volume. If you specify only a subvolume and file name, SQL creates a new index
partition in the current default volume. If you specify a virtual volume, SQL creates
a new index partition in the virtual volume. In all other cases, SQL creates a new
index partition in the physical volume and the new partition is a direct file not
managed by ServerWare SMF.
The ADD PARTITION clause is equivalent to the one-way split form of the MOVE
clause.
add-spec
specifies options for a partition added with the ADD PARTITION clause.
The FIRST KEY clause is required and specifies the primary or clustering key value
for the first key allowed in the new partition. val is a literal compatible with the
data type of the key column that specifies the key value. For clustering keys, specify
multiple vals, in order.
The WITH DATA MOVEMENT clause directs SQL to transfer appropriate rows
from name to dest-part. If you do not specify WITH DATA MOVEMENT,
ADD PARTITION creates an empty partition and returns an error if records exist
within the FIRST KEY declaration of the new partition.
add-spec also includes options that allow you to specify a catalog for the new
partition, a physical volume if ServerWare SMF is installed, and to set the EXTENT
and MAXEXTENTS file attributes for the partition. These options are the same as
options described under move-spec earlier in this entry.
Considerations—ALTER INDEX
•
Authorization and access requirements
To alter an index, you must be a generalized owner of the index and the underlying
table. In addition, you must have authority to read and write to the affected catalogs.
ALTER INDEX executes only if the specified index or partition is accessible.
Unless you are altering file attributes for a partition, all partitions of the index must
be accessible.
Only one DDL statement can operate on a given SQL object (or partition of an SQL
object) at a time. An error occurs if you attempt to execute an ALTER INDEX