SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-15
Creating Table Partitions
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 (excluding
the SYSKEY, if present), and you can specify the columns in any order.
When you create hash partitioned tables, the set of columns you specify for the
partitioning key can be identical to or a subset of the clustering key columns (excluding
the SYSKEY, if present), 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-31 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 Table Partitions
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 these statements, 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.
SQL/MX tables can use range partitioning or hash partitioning. With range partitioning,
you 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, NonStop
SQL/MX uses a hash function on the values of the partitioning key, and each record is
assigned to a partition based on the result. Records are evenly distributed across
partitions, enabling you to maintain partitions of approximately equal size even if you
do not know the range values.
When you create either range-partitioned or hash-partitioned tables, the set of columns
you specify for the partitioning key can be identical to or a subset of the clustering key
columns, excluding the SYSKEY if it is present. Specify these columns in any order to
create a decoupled range partitioned or a decoupled hash partitioned index.
To promote parallel processing of queries and parallel index maintenance, you should
partition data across available disk volumes. For a very large table or a table used at
different geographical sites, use partitions to make the data more accessible and