SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

To promote parallel processing of queries and parallel index maintenance, you should partition
data across available disk volumes. For a very large table or a table used at different geographical
sites, use partitions to make the data more accessible and reduce the time required for table scans
by a factor almost equal to the number of partitions.
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.
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.
• Partitions improve transaction throughput by allowing simultaneous disk access to different
partitions of the same table.
• Partitions require no special access procedures. NonStop SQL/MX manages partition access
for you automatically.
• Partitions enable NonStop SQL/MX to more readily process queries in parallel.
• Partitions allow you to have tables larger than the size of a single disk volume.
Partitioning the indexes of a table enables NonStop SQL/MX to take maximum advantage of
parallel index updates. You can either partition index rows to reside on the same disks (co-location)
or on separate disks.
For more information, see the “Optimizing Index Use” (page 305).
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.
84 Creating an SQL/MX Database