SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-16
Creating Table Partitions
reduce the time required for table scans by a factor almost equal to the number of
partitions.
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.
Naming Partitions
Use the NAME clause of CREATE TABLE or CREATE INDEX to name a partition. The
partition name is an SQL identifier that must be unique among the set of partitions for a
given table or index. When assigned, the partition name cannot be changed for the life
of the partition.
All table and index partitions must have names. If you do not specify one, a
system-generated name is assigned to the partition. For more information, see the
SQL/MX Reference Manual.
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.
Performance Benefits of Partitioning
•
Partitions are independent of one another and only the accessed partition needs to
be available. The query plan stores the primary partition. If the primary partition
cannot be found, the alternate partitions are checked, starting with local partitions.
NonStop SQL/MX does not support the SKIP UNAVAILABLE PARTITION option of
NonStop SQL/MP.
•
Partitions improve transaction throughput by allowing simultaneous disk access to
different partitions of the same table.
Note. SQL/MX tables must have a user-defined clustering key to have partitions. You can
partition any table or index provided its clustering key is not just the SYSKEY.