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-18
Creating and Managing Partitions for SQL/MX
Tables
Recommended Maximum Number of Partitions
HP recommends that you limit the number of partitions in an SQL/MX table or index to
no more than 512. If you exceed this recommended limit, you might get an MXCMP
internal error because of a shortage of virtual memory space.
Prohibition Against Using Floating-Point Columns in
Partitioning Keys
The floating-point data type is an approximate—not an exact—data type. Any key
expression that results in a floating-point value that is close to a partitioning key
boundary might fail to access the correct partition.
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 information about these errors, 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 values of the partitioning key columns for a given row 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 you might create in the future,
not the number in the table from which the rows are being read. This hash value
determines the partition where the row 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