SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
A-47
Considerations—ALTER TABLE
create a new partition. If you specify the FORMAT clause on these types of
operations you will receive a warning and the operation continues.

Dropping partitions of tables

All partitions of a table must be accessible when you drop any partition of the
table, but partitions other than the partition being dropped can be accessed by
other processes while the ALTER TABLE executes.
Dropping a partition requires SQL to update any catalog that describes a
shorthand view in terms of the dropped partition, and to update the catalogs for
the table itself. If any such catalog is unavailable, the ALTER TABLE fails.

Dropping a table partition invalidates a program that uses the table or that uses
a view that depends on the table, unless the program was compiled with
CHECK INOPERABLE PLANS and the table or view has the SIMILARITY
CHECK option enabled.
Invalid programs that reference the table with DEFINEs or with references to
partitions other than the dropped partition can be recompiled (automatically or
explicitly). Programs that reference the table with a physically coded name for
the dropped partition must be modified before they can be recompiled.

If ALTER TABLE fails while attempting to drop a partition, the original table
remains intact and accessible.

ALTER TABLE cannot drop a primary partition, a partition that is not empty, or
a partition in a table with relative or entry-sequenced file organization that is
not the last partition.

Modifying the partition array

Tables and indexes using extended arrays require a catalog of version 320 or
later. SQL DML and DDL statements on tables and indexes with extended
arrays can only be performed from nodes running version 320 or later.
Otherwise, SQL returns an error.
When you modify the partition array of a table, all programs that reference the
table are invalidated. Recompile the programs with NonStop SQL/MP version
320 or later.
When you alter a table from EXTENDED to STANDARD, the data structures
might not fit within the STANDARD format. When this occurs, SQL returns an
error.

Reusing partitions

Use REUSE PARTITION instead of dropping and adding partitions to manage
and reuse disk space. DROP/ADD can take hours to perform if the partition
being reused is from a table with many partitions.

REUSE PARTITION purges all the records in the existing partition. It modifies
the key range of the partition in the label with a new key range to