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-17
Creating Table Partitions
•
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 16-10.
Guidelines for Creating and Placing Your 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.
Using DDL_DEFAULT_LOCATIONS to Distribute Primary
Range Partitions
You can use the DDL_DEFAULT_LOCATIONS system default to specify one or more
default volumes for the primary range partition in a CREATE statement that does not
include a LOCATION clause. If DDL_DEFAULT_LOCATIONS specifies more than one
default volume, it chooses one at random for the location specification. This
arrangement might be particularly useful for an application that creates small,
short-lived tables as part of processing a more complex task.
Because CREATE statements do not allow more than one default location,
DDL_DEFAULT_LOCATIONS never needs to select more than one location for a
given CREATE statement. This approach is a simple but effective method to distribute
primary partitions evenly across the set of volumes specified in
DDL_DEFAULT_LOCATIONS.
For more information about DDL_DEFAULT_LOCATIONS, see the SQL/MX Reference
Manual.