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

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—544536-007
7-16
Creating and Managing Partitions for SQL/MX
Tables
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.
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.
In general, you should use range partitioning when you need to group data by clearly
defined range values. Use hash partitioning when you need to balance the data fairly
evenly across processors and the manner by which it is grouped is not important.
When you create either range-partitioned or hash-partitioned tables, the columns you
specify for the partitioning key can be the same as or fewer than the clustering key
columns, excluding the SYSKEY if it is present. You can 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
reduce the time required for table scans by a factor almost equal to the number of
partitions.
Note. SQL/MX tables must have a user-defined clustering key to have partitions. You can
partition any table or index provided its clustering key is not just the SYSKEY.