SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-25
Considerations—ALTER INDEX

Dropping partitions of indexes

All partitions of an index must be accessible when you drop any partition of the
index, but partitions other than the partition being dropped can be accessed by
other processes while the ALTER INDEX executes.

Dropping 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.

If ALTER INDEX fails while attempting to drop a partition, the original index
remains intact and accessible.

ALTER INDEX cannot drop a primary partition or a partition that is not empty. If
you attempt to drop a partition that contains data, SQL returns error 1411
(Operation cannot be performed against a nonempty partition).

Considerations: Format 2-enabled indexes

Indexes inherit their format from the underlying table. An index can be
considered to be Format 2-enabled if the underlying table is Format 2-enabled.

Existing partitions cannot have their formats altered.

When you create a table with a FORMAT2ENABLED partition array, the table
will have an object version of at least version 350. Therefore, all partitions of
the table and its index partitions must be cataloged in version 350 or later
catalogs for the ALTER INDEX catalog to succeed.

Considerations: ALTER INDEX to add partitions

The default partition format for indexes is based on the partition array value of
the underlying table. For STANDARD and EXTENDED, the default partition
format is 1. For FORMAT2ENABLED, the default partition format is 2. For
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 the extent size of the added 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 SQL error.

You can use the WITH DATA MOVEMENT clause to move data from an
existing partition to the added partition. Data can thus be migrated from Format
1 to Format 2 partitions and from Format 2 to Format 1 partitions.

Considerations: ALTER INDEX to move partitions

The default partition format for indexes is based on the partition array value of
the underlying table. For STANDARD and EXTENDED, the default partition
format is 1. For FORMAT2ENABLED, the default partition format is 2. For