SQL/MP Installation and Management Guide

Reorganizing Tables and Maintaining Data
HP NonStop SQL/MP Installation and Management Guide—523353-004
8-6
Changing Extent Size Values
through 9999 and the other with numbers 10000 through 99999, respectively. When
the last partition becomes full again at a later time, this operation can be repeated.
Consider again the case of the table with three partitions based on the CUSTNUM key.
The partitions are defined by the customer number (CUSTNUM) ranges 1 through
2999, 3000 through 5999, and 6000 through 99999, respectively. As time passes, the
partitions fill 30, 75, and 100 percent of the available space, respectively. You are still
limited, however, to only three disk volumes on which you can place partitions.
Ideally, you want to have three partitions that are each about 60 percent full. To
achieve this goal, you can reorganize the table by moving the partition boundaries. The
FIRST KEY values of the three partitions might now need to indicate the ranges 1
through 4999, 5000 through 7999, and 8000 through 99999, respectively. To perform
this operation, you can use the PARTONLY MOVE option of the ALTER TABLE
statement.
For more information about splitting partitions and moving partition boundaries, see
Splitting, Moving, and Merging Partitions on page 7-20.
If a partition remains empty due to a design or data miscalculation, you can drop the
partition. For key-sequenced tables and indexes, you can drop any empty partition—
even one that lies in the middle of a set of partitions. The one exception is that you
cannot drop the primary partition of a table, although it is empty. For relative and entry-
sequenced tables, however, you can drop only the last partition of the table. For more
information, see Dropping Partitions of Tables and Indexes on page 7-32.
Changing Extent Size Values
Partitioning might not always be required. For instance, if an entry-sequenced table
caused an error 45 (file is full), the error might be based only on the EXTENT SIZE and
MAXEXTENTS values specified when the file was created. If the file is full and does
not need to be spread across disk volumes, you can increase the MAXEXTENTS value
by using the SQLCI ALTER TABLE statement:
>> ALTER TABLE PARTS MAXEXTENTS nnn;
In this statement, nnn is a number greater than the current MAXEXTENTS value. The
maximum value allowed for MAXEXTENTS is 959 for primary partitions and 940 for
secondary partitions.
The same guideline applies to a particular partition of a table; that is, you can increase
the MAXEXTENTS value for a single partition to allow for additional growth.