SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Creating an SQL/MX Database
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
7-15
Creating and Managing Partitions for SQL/MX
Tables
Creating and Using a Partitioning Key
The partitioning consists of the columns you specify in the PARTITION BY clause of
CREATE TABLE and CREATE INDEX statements.
The partitioning key determines how data is distributed into partitions that are
associated with table or index objects. For a range-partitioned table or index, the
partition key uses the FIRST KEY option to specify the beginning of the range for the
table or index partition. The FIRST KEY clause specifies the lowest values in the
partition for columns stored in ascending order and the highest values in the partition
for columns stored in descending order. These column values constitute the
partitioning key.
You specify the first value allowed in the associated partition for that column of the
partitioning key as a literal. If there are more storage key columns than literal items, the
first key value for each remaining key column is the lowest or highest value for the data
type of the column (the lowest value for an ascending column and the highest value for
a descending column).
When you create range partitioned tables, the set of columns you specify for the
partitioning key can be identical to or a subset of the clustering key columns, and you
can specify the columns in any order.
When you create hash partitioned tables, the subset of columns you specify for the
partitioning key can be the same as or less than the clustering key columns, and you
can specify the columns in any order.
For more information about the partitioning key, see Creating Indexes for SQL/MX
Tables on page 7-33 and the SQL/MX Reference Manual.
Partially Decoupling the Clustering Key and the Partitioning
Key
Decoupling the clustering key from the partitioning key in a table or index allows the
columns that make up the keys to differ. NonStop SQL/MX does not support full
decoupling, where all the columns in the two keys differ. However, NonStop SQL/MX
does support partial decoupling, where partitioning key columns can be a subset of
clustering key columns and the columns can be in a different order.
Creating and Managing Partitions for SQL/MX Tables
Create partitions by using the PARTITION clause in a CREATE TABLE statement. Use
the MODIFY utility to split an existing table or index into partitions or to add or drop
partitions. For more information about CREATE TABLE, see the SQL/MX Reference
Manual.
Partitioning enables you to divide a table to your specifications, putting some rows in
one physical location and other rows in other locations. Partitions can reside on one
system or across many systems in a network.