SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
P-19
Partitions
Partitions
A partition is the portion of a table or index that resides on a single disk volume. Each
table or index consists of at least one partition.
An “unpartitioned” table or index is a table or index that consists of exactly one
partition. A “partitioned” table or index is a table or index that consists of more than one
partition.
A “primary partition” is the first partition in a partitioned table or index. Other partitions
are called “secondary partitions.” If the order is ascending, the primary partition
contains the lowest set of key values in the table or index; if the order is descending,
the primary partition contains the highest set of key values.
A partition name, like a table or index name, is a Guardian name. Each fully expanded
partition name must be unique within the network. If a table or index consists of more
than one partition, the subvolume and file name portions of the name of each partition
in the table or index must be identical. The combination of disk volume and node name
will be different for each partition, reflecting the fact that the different partitions reside
on different disk volumes.
You can create a partitioned table with CREATE TABLE. For a table with
key-sequenced file organization, you can use the PARTONLY MOVE clause of ALTER
TABLE to break the table into partitions or to break a partition into additional partitions.
For a table with entry-sequenced or relative file organization, you can add a partition to
the end of the table with the ADD PARTITION clause of ALTER TABLE. To create a
partitioned index, use CREATE INDEX or the PARTONLY MOVE clause of ALTER
INDEX.
A partition’s format is based on the partition array value of its table. If the table’s
partition array value is STANDARD, its partitions will be Format 1. If it is
FORMAT2ENABLED, its partitions can be of either format, although they will default to
Format 2. A Format 1 partition’s size is limited to two gigabytes. Format 2 partitions
can be up to one terabyte or the limit of a single disk volume, whichever is less.
You can configure tables and indexes that are Format 2 enabled, which lets them have
Format 1 or Format 2 partitions, with CREATE TABLE and CREATE INDEX. You can
also add new partitions of either format to existing tables and indexes with ALTER
TABLE and ALTER INDEX. Use the MOVE clause of ALTER TABLE or ALTER INDEX
to move data between existing partitions and newly created partitions of either format.
These rules apply to partitions:

The FIRST KEY value of a new partition cannot duplicate the FIRST KEY value of
another partition of the table.

You cannot create a partition in a nonaudited volume, even if the table that
includes the partition is not audited.

You cannot partition key-sequenced tables that use SYSKEY as the primary key.