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-20
Creating and Managing Partitions for SQL/MX
Tables
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.
Special Considerations for Decision Support Systems (DSS)
Applications
DSS applications typically require periodic addition and deletion of data. Further,
access must be well balanced. For NonStop SQL/MX, the partitioning key can be
different from the clustering key. The choice of leftmost primary key column—and the
partitioning strategy—can greatly affect access. Choose a leftmost key column that
minimizes contention while allowing acceptable load and delete performance.
Possibilities include a date column (if access is distributed evenly across date values),
a nondate column in the clustering key (if access across the date column is weighted
toward specific ranges), or an artificial partition number value, with rows distributed
across all partitions.
Further, when defining partitions for a DSS application, consider one or more of these
strategies:
•
Mirror the volume associated with the primary partition to maximize availability.
Queries and programs that reference the primary partition require its availability
when accessing data.
•
Mirror the volume that contains the SMD tables.
•
Configure mirrored disks on separate channels to maximize performance. Use this
strategy for volumes containing primary and secondary partitions where possible.
Examples for Creating Partitioned SQL/MX Tables
Example for Creating an SQL/MX Table With a Single Range Partition
CREATE TABLE cat1.sch1.ordersr
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
location $DATA01;