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-18
Creating Table Partitions
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.
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;
Example for Creating an SQL/MX Table With Multiple Range
Partitions
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
partition(
add first key ('Chicago') location $DATA02,
add first key ('New York') location $DATA03 );