SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-46
Considerations—ALTER TABLE

Similar to other DDL operations, all partitions of the table must be available for
the operation to complete.

Only key-sequenced tables can be Format 2-enabled tables. If you attempt to
specify FORMAT2ENABLED for a relative or entry-sequenced table, you
receive an error.

If you attempt to convert a Format 1-enabled table to Format 2-enabled where
the existing number of partitions is greater than that supported by Format 2,
you will receive an error. Because modifying the partition array of a table also
implicitly modifies the partition arrays of the indexes, the indexes on the table
are subject to the same limits. An error is returned if any indexes violate the
limits.

Modifying the partition array of a table to be FORMAT2ENABLED causes the
object version of the table and all its indexes to become at least version 350.
Therefore, all partitions of the table and its indexes must be cataloged in
version 350 or later catalogs to modify the partition array to
FORMAT2ENABLED. Modifying the partition array of a table to be STANDARD
or EXTENDED causes the object version of the table to be recalculated based
upon the other features associated with the table.

When converting a table from Format 1 enabled to Format 2 enabled, the row
length of the table or an index on the table might not fit within a Format 2 block
because the amount of overhead space for the block header is larger for
Format 2 files. The ALTER TABLE statement fails when this condition occurs.

Considerations: ADD PARTITION to specify partition format

The default partition format is based on the partition array value for
key-sequenced tables. 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.

The format and file parameters such as extent size of the added partition must
be consistent with the existing table for the ALTER TABLE command to
succeed. For example, if you explicitly specify the new partition as FORMAT 2
when the partition array is STANDARD or EXTENDED you will receive an
error.

The WITH DATA MOVEMENT clause can be used to move data from an
existing partition to the added partition. Data can be migrated from Format 1 to
Format 2 partitions and from Format 2 to Format 1 partitions.

Only key-sequenced tables can be Format 2 enabled. Relative and
entry-sequenced tables can only be Format 1 enabled. If you attempt to add a
Format 2 partition to a relative or entry-sequenced table you will receive an
error. Specifying FORMAT 1 for a relative or entry-sequenced partition is
allowed.

The FORMAT clause is ignored if specified for a merge or one-way move
(move partition boundary) operation because these types of moves do not