SQL/MP Installation and Management Guide

Adding, Altering, Removing, and Renaming
Database Objects
HP NonStop SQL/MP Installation and Management Guide—523353-004
7-32
Dropping Partitions of Tables and Indexes
Dropping Partitions of Tables and Indexes
Use the ALTER TABLE statement with the DROP PARTITION option to drop a partition
of a key-sequenced, entry-sequenced, or relative table and the ALTER INDEX
statement with the DROP PARTITION option to drop a partition of an index.
Determining When to Drop a Partition
When all information in a partition becomes obsolete, or when a database design
deficiency leaves a partition continually empty, a reference to a table or index defined
across this partition results in an unnecessary message being issued to the partition.
For example, an index label is updated to include the names of all index partitions
whenever the label for an associated object is altered. This update can happen, for
example, when a table is backed up or restored or when an index is added or dropped.
(Simply recompiling a program does not update the labels for the referenced objects.)
This unnecessary message results in a correspondingly longer access time to the table
or index. In such circumstances, you might want to drop this partition while leaving the
others defined for the object intact.
Guidelines for Dropping Partitions
You can drop partitions of tables and indexes within these guidelines:
•
The partition must be empty.
•
The partition cannot be the primary partition of the table or index.
•
For a relative or entry-sequenced table, you can drop only the last partition of that
table.
•
All partitions of the table or index must be available when you enter the ALTER
statement with the DROP PARTITION option.
•
Dropping a partition of a table also drops the corresponding partition of any
protection views defined on the table.
•
Dropping a partition of a table invalidates all programs that use the table or a view
that depends on the table unless a program was compiled with the CHECK
INOPERABLE PLANS option and the similarity check is enabled for the table and
any associated protection views. (Similarity checking is not available for shorthand
views.) For more information, see Using Similarity Checks on page 10-15.
•
Dropping a partition of an index invalidates all programs that use the underlying
table or a view that depends on that table unless a program was compiled with the
CHECK INOPERABLE PLANS option and the similarity check is enabled for the
table and any associated protection views. (Similarity checking is not available for
shorthand views.)
•
You should include steps to explicitly SQL compile dependent programs to avoid
automatic recompilation and to return the application to a valid state.