SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

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, see the “Creating Indexes for SQL/MX Tables” (page 95) 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, 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 disk
volumes 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. 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.
82 Creating an SQL/MX Database