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-17
Creating and Managing Partitions for SQL/MX
Tables
Restrictions on Creating and Placing Partitions

When you create a hash-partitioned table with a single partition, you can add
additional hash partitions to the table. However, you cannot change the partitioning
scheme to range partitioning.

If you do not specify a partitioning scheme when you create a single-partition table,
you can only add range partitions to the table. You cannot add hash partitions to
the table.

You cannot create SQL/MX tables or other SQL/MX objects on an SMF volume.
Nor can you use SMF to manage the distribution of partitions across physical disk
volumes.

You can put more than one partition on a single disk, but this approach slows down
parallel query processing and is not recommended in a production environment.
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.
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.
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.