SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-30
Controlling the Number of Key Columns Used by
MDAM
In addition to the open-on-demand feature, NonStop SQL/MP also ensures that tables
are automatically created to provide local autonomy. Local autonomy ensures that you
can access local data regardless of the availability of other local dependent objects or
remote dependent objects, if the local data that is available can fully satisfy your
request.
With local autonomy, the compiler stores information about partitions that underlie
tables and indexes, enabling the file system to open any available partitions as
needed.
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 key columns than you
want. On these occasions, you can use the ACCESS PATH . . . MDAM ON option in a
CONTROL TABLE directive, and apply the USE option to specify the number of key
columns for MDAM to use.
Suppose four columns of the key have MDAM predicates. You have determined the
unique entry count of the fourth key column used in a predicate. (You have found the
count by updating statistics and querying the catalog table for the column). If you do
not want MDAM to step through all the different values for the fourth column, you can
specify the number of columns for MDAM to use. If you specify three, then only
predicates on the first three 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 DEFAULT for the number of key columns allows MDAM
to choose the number of key columns.
For more information on MDAM, see Transformation of Predicates
on page 3-4.
Controlling MDAM’s Use of DENSE or SPARSE
Algorithms
When you use a CONTROL TABLE directive with MDAM ON, you can specify whether
the optimizer should use an adaptive DENSE or SPARSE algorithm for all columns
during row access. An adaptive algorithm is an algorithm chosen by the optimizer but
might not be chosen by the executor. For example, if the optimizer chooses a DENSE
algorithm and the executor finds a DENSE algorithm is inefficient for accessing a
certain column, the executor adapts by switching to a SPARSE algorithm. It switches
back to the DENSE algorithm as soon as it finds the value it is seeking.
You can determine the density or sparsity of data by the values in a column. For
example, if column A contains the values 1, 2, 3, and 4, it has dense data distribution.