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

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Installation and Management Guide—523723-004
4-9
When to Use Range Partitioning
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.
When to Use Hash Partitioning
Use hash partitioning if your data is not easily distributed among ranges or to evenly
distribute data across a specified number of partitions. Creating and using hash
partitions gives you a highly tunable method for data placement because you can
influence availability and performance by spreading these evenly sized partitions
across I/O devices (striping).
Hash partitioning achieves many of the scan reduction benefits of range partitioning
without requiring you to know the distribution of the partitioning key in advance. In
addition, use hash partitioning in a “decoupled” fashion to cluster data on a key other