SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—523725-004
6-83
SQL/MX Tables
In this scenario, suppose that you have a database with ten 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 ten partitions and twenty partitioning key values. You
would expect that each partition would be assigned two 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 three, four, or more partitioning key values. Other partitions can be
assigned two, one, or no partitioning key values. Even if there are many records, there
could be partitions with more than twice the expected number of records, and partitions
with 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 which:
•
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-75, CREATE INDEX Statement on page 2-54, and ALTER TABLE Statement
on page 2-11. For a description of this utility including information about which
attributes can be set for individual partition, see MODIFY Utility on page 5-43.
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.