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-17
Creating and Managing Partitions for SQL/MX
Tables
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
SQL/MP.
•
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 about strategies for improving index performance, see Optimizing
Index Use on page 15-16.
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. 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.