SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-18
The Effect of the MultiDimensional Access Method
(MDAM) on Costs
BETWEEN 5 AND 10, a positioning takes place for each value of B between 5 and
10. The number of positionings that take place for a predicate set are totaled as part of
the cost.
A positioning does not take place if a range is for the last-used key column.
To estimate the cost of an IN predicate within a predicate set, the optimizer converts it
to an IN predicate equivalent. A positioning is done on each of the equal predicates
and the number of positionings is added to the cost.
The Sparsity or Density of the Data
The optimizer considers the sparsity or density of data whenever a range is present. If
the data in the predicate set is sparse, two positionings are done. The first positioning
finds the next value for a column. The second one accesses the data using the values
for all of the other columns. Based on the statistics, the optimizer estimates the number
of positionings that the executor will do.
If the data is dense, SQL positions only once. The number of key columns and the
predicate selectivity are also considered.
A dense range has the same cost as an IN predicate with the same values, as in:
B BETWEEN 5 and 10
would be costed the same as
IN (5, 6, 7, 8, 9, 10)
The Last-Used Column in a Key
When the last-used key column is a range, the executor reads the entire range as a
unit, and no key positionings are required for each possible value. The cost of reading
a range on the last key column is included in the cost of an access.
Estimating MDAM Costs for Multiple Predicate Sets
For multiple predicate sets, the cost for each predicate set is completed separately,
and the costs are accumulated. For joins, the cost of each predicate set is multiplied by
the number of rows used from the outer table.