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-16
Creating and Managing Partitions for SQL/MX
Tables
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.
Performance Benefits of Partitioning

Partitions are independent of one another and only the accessed partition needs to
be available. The query plan stores the primary partition. If the primary partition
cannot be found, the alternate partitions are checked, starting with local partitions.

Partitions improve transaction throughput by allowing simultaneous disk access to
different partitions of the same table.

Partitions require no special access procedures. NonStop SQL/MX manages
partition access for you automatically.

Partitions enable NonStop SQL/MX to more readily process queries in parallel.

Partitions allow you to have tables larger than the size of a single disk volume.
Partitioning the indexes of a table enables NonStop SQL/MX to take maximum
advantage of parallel index updates. You can either partition index rows to reside on
the same disks (co-location) or on separate disks.
For more information about strategies for improving index performance, see Optimizing
Index Use on page 15-17.
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.