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

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—544536-007
7-19
Creating and Managing Partitions for SQL/MX
Tables
datatype as the planned table. You can use the CAST function to force a given value to
a particular datatype.
HASHPARTFUNC is also sensitive to the number of partitions you specify. Therefore,
when using the HASHPARTFUNC, make sure this number matches the exact number
of partitions you plan for your new or modified table.
You can use HASHPARTFUNC on a nonpartitioned, range-partitioned, or
hash-partitioned table to determine what the data distribution would be if you were to
hash partition the table in various ways, with different numbers of partitions and
different partitioning keys.
Guidelines for Creating a Partitioning Key
The partitioning key consists of columns from the clustering key for the planned table.
However, the partitioning key columns do not have to be in the same order as the
clustering key, and the partitioning key uses the same columns or a subset of the
columns of the clustering key.
Guidelines for Creating a Balanced Hash-Partitioned Table
To create a hash-partitioned table with a fairly balanced distribution of data across the
partitions:
•
Make sure that you have an appropriate number of partitions relative to the unique
entry count of the partitioning key. The partitioning key unique entry count refers to
the total number of unique values derived from the columns that comprise the
partitioning key.
•
Make sure that the partitioning key generates a sufficiently large unique entry
count.
To create a hash-partitioned table where the distribution of data across the partitions is
fairly balanced, make sure that the unique entry count of the partitioning key is at least
50 times greater than the number of partitions. For example, if the single-column
partitioning key for a table with 1000 rows produces a unique entry count of 1000, you
should distribute the data across no more than 20 hash partitions.
This guideline poses problems for hash-partitioned tables with a relatively low unique
entry count. Also, the lower a table’s unique entry count, the higher the probability for
data skew. For example, data skew is proportionately greater in a table with a unique
entry count of 100 and two partitions than in a table with a unique entry count of 1000
and 20 partitions. One solution is to use a partitioning key that produces a higher
unique entry count.
Using DDL_DEFAULT_LOCATIONS to Distribute Primary
Range Partitions
You can use the DDL_DEFAULT_LOCATIONS system default to specify one or more
default volumes for the primary range partition in a CREATE statement that does not