SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-17
Controlling the Number of Key Columns Used by
MDAM
•
Order your key columns by the columns that you access most frequently. If you
frequently perform a query that uses department number, include the DEPTNUM
column in your key-column index.
For example, if the EMPLOYEE table contains the columns STATE, JOBCODE,
DEPTNUM, LAST_NAME, FIRST_NAME and you frequently perform queries
based on JOBCODE, STATE, and DEPTNUM, place the columns in that order.
Controlling the Number of Key Columns Used by MDAM
In most cases, you should allow MDAM to choose the number of key columns to use
for MDAM processing. Occasionally, MDAM chooses more or fewer key columns than
you expect. On these occasions, you can force MDAM use with the CONTROL
QUERY SHAPE statement to specify the number of key columns for MDAM to use. For
information about using CONTROL QUERY SHAPE, see Section 5, Forcing Execution
Plans.
Suppose that four columns of the key have predicates. You have determined the
Unique Entry Count (UEC) of the third key column used in a predicate. (You have
found the count by updating statistics or querying the count for the column.) If you do
not want MDAM to step through all the different values for the third column, you can
specify the number of columns for MDAM to use. If you specify two, only predicates on
the first two key columns are used.
If you specify a number of columns that is less than or equal to zero, SQL returns an
error. If the number you specify exceeds the number of key columns available for the
index, the optimizer uses the maximum number of key columns usable by MDAM for
each predicate set. Specifying SYSTEM for the number of key columns enables
MDAM to choose the number of key columns.
MDAM’s Use of DENSE and SPARSE Algorithms
A dense key column has all (or almost all) the possible values for a column. If a column
has 100 unique values and the column value ranges from 0 through 99, the column is
considered dense. When a dense column is recognized, MDAM has to add only one to
the previous value to find the next value in the column.
A sparse key column has missing possible values. A column is considered sparse
when the number of actual values in the column is small relative to the set of all
possible values. Sparse columns contain gaps in the ranges specified for the key
column. The optimizer detects these gaps by analyzing the UEC and high and low
values of the column.
The compiler and executor use adaptive DENSE and SPARSE algorithms for the
leading key column only. The algorithm chosen by the compiler might not be rigidly
followed by the executor. For example, if the optimizer chooses a DENSE algorithm
and the executor finds the DENSE algorithm is inefficient for accessing the column, the
executor adapts by switching to a SPARSE algorithm when it discovers that many
values are missing. The executor chooses the appropriate algorithm for the density or
sparseness of data.