SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-26
Examples—ALTER INDEX
relative and entry-sequenced tables, the partition format is always 1 because
such tables cannot have a FORMAT2ENABLED partition array.

The format and file parameters, such as extent size of the new index partition,
must be consistent with the index format for the ALTER INDEX command to
succeed. For example, if you explicitly specify a partition as FORMAT 2 when
the partition array is STANDARD or EXTENDED you will receive an error.

The FORMAT clause can be specified for a simple move, one-way split, or
two-way split operation.

SQL ignores the FORMAT clause if you specify it for a merge or a one-way
move (move partition boundary) operation because these types of moves do
not create a new partition. If you specify the FORMAT clause on these types of
operations you will receive a warning and the operation continues.
Examples—ALTER INDEX

This example changes the value of MAXEXTENTS and deallocates the disk space
for the index XORDCUS:
ALTER INDEX \SYS1.$VOL1.SALES.XORDCUS
MAXEXTENTS 300 DEALLOCATE;

This example changes the maximum number of extents for index XEMPNAME to
200. It also sets the SERIALWRITES attribute to specify serial mirror writes for
operations on the index:
ALTER INDEX \SYS.$VOL1.PERSNL.XEMPNAME
MAXEXTENTS 200 SERIALWRITES;

This example renames the index XEMP:
ALTER INDEX XEMP RENAME XEMPID;

This example moves an index partition, specifying WITH SHARED ACCESS to
keep the partition accessible to other processes during most of the move:
ALTER INDEX $DISK1.USERS.XDATA PARTONLY MOVE TO $DISK2
WITH SHARED ACCESS NAME MOVE2D2 COMMIT BY REQUEST;
...
CONTINUE MOVE2D2 ONCOMMITERROR COMMIT BY REQUEST;
The ALTER INDEX statement specifies COMMIT BY REQUEST so that the user
can control entry to the commit phase of the operation, which locks out other
processes. The CONTINUE statement starts the commit phase, directing SQL to
return control to the user if a retryable error occurs during the phase.

This example moves the latter portion of an index partition into a new partition (a
one-way split):
ALTER INDEX =XPART_LOC PARTONLY MOVE
FROM KEY "I00" TO =XPART_EUROPE
EXTENT (8,8) SLACK 20;