SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Creating an SQL/MX Database
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
7-19
Creating and Managing Partitions for SQL/MX
Tables
Guidelines for Creating a Balanced Hash-Partitioned Table
To create a hash-partitioned table with a fairly balanced distribution of data across the
partitions:

Make sure that you have an appropriate number of partitions relative to the unique
entry count of the partitioning key. The partitioning key unique entry count refers to
the total number of unique values derived from the columns that comprise the
partitioning key.

Make sure that the partitioning key generates a sufficiently large unique entry
count.
To create a hash-partitioned table where the distribution of data across the partitions is
fairly balanced, make sure that the unique entry count of the partitioning key is at least
50 times greater than the number of partitions. For example, if the single-column
partitioning key for a table with 1000 rows produces a unique entry count of 1000, you
should distribute the data across no more than 20 hash partitions.
This guideline poses problems for hash-partitioned tables with a relatively low unique
entry count. Also, the lower a table’s unique entry count, the higher the probability for
data skew. For example, data skew is proportionately greater in a table with a unique
entry count of 100 and two partitions than in a table with a unique entry count of 1000
and 20 partitions. One solution is to use a partitioning key that produces a higher
unique entry count.
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.
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