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

Therefore, NonStop SQL/MX does not support the use of floating-point data type columns in
partitioning keys. Attempts to create SQL/MX tables or indexes with floating-point partitioning key
columns fail with error message 1124. Attempts to perform DML from NonStop SQL/MX on
SQL/MP tables with floating-point partitioning key columns fail with error message 1120. For more
information, see the SQL/MX Messages Manual.
Using HASHPARTFUNC to Plan and Build Hash-Partitioned Tables
NonStop SQL/MX uses the HASHPARTFUNC function to hash partition data. NonStop SQL/MX
passes column values and the number of partitions, n, to HASHPARTFUNC. The function then
returns a hash value in the range 0 through n-1, where n is the number of partitions. This hash
value determines the partition where a row with the given colum value(s) will be stored.
For a description of HASHPARTFUNC function syntax and examples, see the SQL/MX Reference
Manual.
When planning a new or modified hash-partitioned table, you can use HASHPARTFUNC to:
• Identify the hash partition into which a given row will go.
• Determine the number of rows that will be assigned to a given number of hash partitions.
• Experiment with different combinations of partitioning key columns and numbers of hash
partitions to find the one that evenly balances distribution of your table data.
• Preorder existing table data for efficient insertion into a new hash-partitioned table.
HASHPARTFUNC is evaluated only on the variables you enter with the function. It does not refer
to any underlying table.
HASHPARTFUNC is sensitive to the datatypes of its parameters. Therefore, when using
HASHPARTFUNC, make sure the variables passed to it are of exactly the same 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,
Creating SQL/MX Tables 85