NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
A-43
Considerations—ALTER TABLE
°
Tables and indexes using extended arrays require a version 320 or later catalog.
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 accommodate
database growth. Because each partition needs to know the location and key
range of other partitions, REUSE PARTITON updates the label of all the
partitions with the new key range.
°
After REUSE PARTITION is performed, the reused partition is empty. If an
error is encountered during REUSE, the data in the reused partition may not be
recoverable. It is recommended that you back up data before performing
REUSE PARTITION.
°
During the operation of reusing a partition, the partition becomes inaccessible
for read and write by DML statements until the operation is done.
°
Only a partition with key-sequenced file organization can be reused. A partition
with relative or entry-sequenced file organization cannot be specified in
REUSE.
°
REUSE PARTITION can only be performed against the secondary partition of a
partitioned table. It cannot be performed on a primary partition or any
partitioned indexes.
°
REUSE PARTITION cannot be executed within a user transaction. This
prevents a user from performing a rollback of the transaction during the
REUSE. Instead, it will be executed within the transaction started by the catalog
manager.
°
Only a partitioned table without any dependent objects can be reused. A table on
which indexes or views are defined cannot be used.
°
If records exist within the FIRST KEY specified, SQL returns an error. The
partition will not be used and the partitions will be intact. The key ranges of
each partition cannot overlap.