SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-80
SQL/MX Tables
partitions, it is possible that records are not (for example, if data is skewed within
partitioning key values).
In this scenario, suppose that you have a database with 10 partitions and 1,000 unique
partitioning key values. Each partition will be assigned approximately 100 partitioning
key values, plus or minus. However, if one of the partitioning key values is in 20
percent of the rows and the other 999 partitioning key values are distributed evenly
among the other 80 percent of the rows, one partition will be assigned at least 20
percent of the rows, twice as many than would be expected in a random distribution.
Another example is when the unique entry count of the partitioning key values is
relatively small compared to the total number of partitions. In this scenario, suppose
that you have a database with 10 partitions and 20 partitioning key values. You would
expect that each partition would be assigned 2 partitioning key values, but because the
distribution is a random distribution based on the hash value of the partitioning key,
some partitions are assigned more values and others fewer. Some partitions can get 3,
4, or more partitioning key values. Other partitions can be assigned 2, 1, or no
partitioning key values. Even if there are many records, some partitions could have
more than twice the expected number of records, and could partitions could have no
records.
You control how values are distributed with the partitioning key. In another scenario,
suppose that you want to distribute a database over many partitions, based on a
unique telephone number that consists of an area code, an exchange, and a number
(nnn-nnn-nnnn). If you use the area code values as the partitioning key, the
distribution will be uneven because there are not many different area code values, and
the number of different values is small in relation to the number of partitions. Instead,
use the entire telephone number because it has so many more unique values.
Hash partitioning enables you to maintain partitions of approximately equal size, even
if you do not know range values, if you have a partitioning key that:
•
Does not have much data skew.
•
Has many values relative to the number of partitions. The partitioning key should
have at least 50 times as many distinct values as there are partitions.
For more information, see PARTITION Clause
on page 7-5 CREATE TABLE Statement
on page 2-74, CREATE INDEX Statement on page 2-52, and ALTER TABLE Statement
on page 2-10. For a description of this utility including details about which attributes
you can set for individual partition, see MODIFY Utility on page 5-47.
Automatically Creating Partitions
MXCS and JDBC/MX users can automatically create hash-partitioned SQL/MX tables
with the Partition Overlay Specification (POS) feature of the CREATE TABLE
statement. NonStop SQL/MX does not support automatic creation of range-partitioned
tables.
Applications can control whether POS is enabled, the number of partitions, and the
physical location of the partitions.