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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-62
Examples of HashPartFunc
•
This example uses the EMPLOYEE table from the sample database to show the
partition number of each row based on the EMPNUM for four partitions and
ordered by partition and EMPNUM.
This example shows how HashPartFunc can reveal data skew. Because the
EMPLOYEE table has only 62 different EMPNUMs, when it is partitioned four
ways, the last partition is somewhat shorter than the others, because the number
of unique entry counts (UECs) from the partitioning key is not sufficiently greater
than the number of partitions.
>>SELECT empnum, HashPartFunc (empnum for 4)
+>FROM employee
+>ORDER by 2, empnum;
Employee/Number (EXPR)
--------------- ----------
29 0
43 0
75 0
109 0
203 0
208 0
209 0
219 0
221 0
224 0
226 0
229 0
232 0
235 0
343 0
557 0
568 0
991 0
39 1
89 1
93 1
201 1
210 1
217 1
223 1
225 1
228 1
230 1
233 1
321 1
337 1
990 1
992 1
994 1
995 1
1 2