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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-61
HASHPARTFUNC Function
HASHPARTFUNC Function
HashPartFunc is the function NonStop SQL/MX uses to hash partition data.
HashPartFunc returns the number of the partition to which a row identified by the
specified partitioning key would belong if the table were hash partitioned.
HashPartFunc is an SQL/MX extension.
partitioning-key
is the prospective partitioning key value of a row from a partitioned or
nonpartitioned table. partitioning-key is a comma-separated list of values
that make up the partitioning key.
num-partitions
is the number of partitions that you might create in the future, not the number in the
table from which the rows are currently being read.
Considerations for HashPartFunc
Cast the partitioning key values to their declared types, because HashPartFunc is
sensitive to the data type.
HashPartFunc is evaluated based only on the variables you enter rather than the
underlying table. You can use this function on a nonpartitioned table to find out what
the data distribution would be if you were to hash partition the table in various ways;
that is, with different numbers of partitions and different partitioning keys.
You can also use HashPartFunc to preorder data for efficient insertion into a
hash-partitioned table with a specified number of partitions. The most efficient insertion
times usually are achieved when both of these conditions are met:
•
All of the data rows destined for a particular partition are grouped together.
•
Within each grouping, data rows are sorted by the clustering key of the destination
table. (The clustering key is typically the same as the primary key, but it might be
different if, for example, the destination table is created with a STORE BY key that
is different from the primary key.)
When you use HashPartFunc, partition numbers are mapped to the physical partitions
in the order in which the partitions will be added when the desired destination table is
created with the CREATE TABLE statement.
Examples of HashPartFunc
•
HashPartFunc returns the partition number as a value between 0 and
(num-partitions - 1).
HashPartFunc(partitioning-key FOR num-partitions)