SQL/MX Release 2.0 Best Practices

Design Guidelines and Considerations 15
• Join efficiency
• Insert efficiency
• Data clustering
You must determine whether to create the index before or after the data is loaded to the table. If you
create the index first, the index will be a constraint, limiting the ability to insert duplicate data. If you
create the index after the data is loaded to the table, you may need to eliminate any duplicates prior to
creating the unique index.
The primary key (when different from the store-by value) created during the CREATE TABLE statement,
creates a single partition index on the same drive as the first store-by partition. This single partition index is
a physical index that can be modified, but you must use the index name that the system has generated
for the physical index. To get the index name, use SHOWDDL on the table. The primary key can only be
modified by using range partitioning. You cannot use hash partitioning to modify a primary key.
You cannot update a column that is part of the non-droppable primary key or the clustering key. To
update a column, you should plan to have the data to be modified defined as an index instead of as a
primary key or clustering key. Otherwise, to update the data in the column, you must follow these steps:
1. Select the rows containing the columns to be modified from the table to program storage variables
or a temporary table.
2. Delete the rows from the original table.
3. Update the columns in the program or temporary table.
4. Insert the rows back into the original table from the program or temporary table.
At this time, you cannot use NOT DROPPABLE on a primary key when the primary key differs from the
store-by key. Usually, it is recommended that you use NOT DROPPABLE because it aids performance. A
column that potentially contains a null value is not checked for an additional constraint. This suggestion
also applies to making columns NOT NULL NOT DROPPABLE.
For better design and functionality, you should create a unique index to function as the primary key
(however, only if the store-by key value differs from the primary-key value). A unique index allows you to
use hash partitioning if desired, and to easily modify the locations of the partitions.
If the primary-key value and the store-by key value are the same, the data is partition-distributed in the
format requested by the STORE BY syntax.
Data Access Efficiency
As described in this document, data access efficiency is the degree to which query predicates restrict
the search space of the data selected. You must have prior knowledge of the most common predicates
that queries will use. Look for predicates that are common to all or most queries. These predicates restrict
the search space for the majority of database queries. These columns become primary candidates for
determining the key order.
Cardinality
Cardinality is the number of unique values that can appear for a particular column. For example, a
demographic column might contain only 10 distinct values, although that column is included in several
million fact table rows. Hence, the column’s cardinality is 10.
Cardinality is an important consideration for MDAM query technology processing. Low-cardinality
columns tend to enable efficient MDAM usage. Cardinality is most useful for avoiding full table scans
when the query omits predicates on leading key columns.
When you have a choice, place columns that have low cardinality as high in the key order as possible,
and in increasing order of cardinality. This arrangement ensures the greatest degree of MDAM query
technology processing when high-order key columns are omitted from the query. You can place columns
that have low cardinality after columns that have higher cardinality, providing that predicates are always
supplied on the leading high-predicate columns. MDAM query technology is used for the low-cardinality
columns when those columns are omitted from the query predicates. However, generally you should give
preference to data-access efficiency considerations over cardinality. That is, if you are certain that a