SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Creating an SQL/MX Database
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
7-18
Creating and Managing Partitions for SQL/MX
Tables
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.