SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Planning Table and Index Partitioning
With medium to very large databases, it is often necessary to use partitioned tables and indexes
to break down the data into smaller and more manageable units. Each partition can be managed
individually and can function independently of the other partitions.
Partitioning can provide significant benefits to a wide variety of applications by improving
manageability, performance, and availability. It is not unusual for partitioning to improve the
performance of certain queries or maintenance operations by an order of magnitude.
Range Partitioning and Hash Partitioning
NonStop SQL/MX supports range partitioning and hash partitioning.
Range partitioning uses ranges of column values to map rows to partitions. Range partitions are
ordered by defining the lower and upper boundary for a specific partition. Rows from all tables
or indexes that contain key values that fall within a certain range 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.
Fo more information, see the SQL/MX Reference Manual.
Using 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.
Using 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
34 Understanding and Planning SQL/MX Tables