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

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
4-8
When to Use Range Partitioning
are placed on a particular disk. For example, rows for employee numbers 1-500 might
be placed on disk 1, rows for employee numbers 501-1000 placed on disk 2, and so
on. This approach offers good performance for range-based queries and reasonable
performance for exact-match queries involving the partitioning key. You can also put
multiple partitions on the same disk.
Hash partitioning uses a hash function on the partitioning columns to stripe data into
partitions. The hash function is applied to the value of a table or index’s clustering or
partitioning key. The output of this function causes the data for that row to be targeted
for placement on a particular disk. The user has no control over row-to-partition
mapping.
For information about creating and managing partitions, see the SQL/MX Reference
Manual.
When to Use Range Partitioning
Range partitioning is useful when your data has logical ranges and boundaries into
which it can be subdivided and distributed (for example, months of the year). Range
partitioning is optimal when the data is evenly distributed across the range. If
partitioning by range causes partitions to vary dramatically in size because of unequal
distribution (for example, many more records for the year 2004 than 1999), consider
hash partitioning.
Advantages of Range Partitioning

The location of data among the range partitions is intuitive and easy to discern.
Partitioned data is not randomly spread across the entire database as it is with
hash partitioning.

Range partitioning provides superior performance for sequential access, for point
queries on partitioning attributes, and for range queries on partitioning attributes
where only one or a few disks need to be accessed.
Disadvantages of Range Partitioning

Each range partition must be defined by the user.

Keeping the range partitions balanced and equal in size might require considerable
time and effort.

The size of range partitions can differ substantially because of the amount of data
that is mapped to each specific partition (“hot spots”). Partitions that contain the
most recent data tend to be larger and queried much more frequently than
partitions that contain older data. This can cause suboptimal performance for
certain operations like DML.