SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-79
Partitions
Partitions
Typically, there is a one-to-one correspondence between a table definition and a
physical file. However, large tables, or tables with special performance requirements,
might require partitioning into multiple physical files.
A partition is the part of a table or index that resides on a single disk volume. Each
table or index consists of at least one partition. A nonpartitioned table or index consists
of exactly one partition. A partitioned table or index consists of more than one partition.
You create partitions by using the PARTITION clause in an SQL/MP or SQL/MX
CREATE TABLE; or CREATE INDEX statement, SQL/MP ALTER statement, or
SQL/MX MODIFY utility.
Partitioning character columns must derive from the ISO88591 character set and
cannot be floating-point data columns.
SQL/MP Tables
A partition name, like a table or index name, is a Guardian name. If a table or index
consists of more than one partition, the subvolume and file name portions of the name
of each partition must be identical. Different partitions reside on different volumes. You
cannot partition key-sequenced tables stored only by the SYSKEY.
You must specify the FIRST KEY, or first possible values, for each partition of key-
sequenced tables. The primary partition contains the lowest set of key values if the first
column of the key is stored in ascending order or the primary partition contains the
highest set of key values if the first column of the key is stored in descending order.
For a key-sequenced table, you can use the PARTONLY MOVE clause of the SQL/MP
ALTER TABLE statement to break the table into partitions or to break a partition into
additional partitions.
See CREATE TABLE Statement, ALTER TABLE Statement, and Partitions in the
SQL/MP Reference Manual.
SQL/MX Tables
If an index or a table is stored by a user-specified key, either a primary key or a key
column list, you can specify partitioning for the index or table.
NonStop SQL/MX supports range partitioning and hash partitioning. With range
partitioning, you use a FIRST KEY definition to define key ranges for each partition,
and each record is assigned to the partition whose range includes the value of its
partitioning key.
With hash partitioning, SQL uses a hash function on the values of the partitioning key,
and each record is assigned to a partition based on the result. Partitioning key values
are distributed among all partitions in a generally balanced way. The distribution is
random: some rows are assigned more partitioning key values, and some rows are
assigned fewer. However, although partitioning key values are balanced among the