SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-5
Predicate Selectivity
Selectivity for Range Predicates
When a range is used in a predicate, selectivity is calculated as follows:
1. The SECONDLOWVALUE is subtracted from the SECONDHIGHVALUE. The
result is the total number of values in the range. For example, if the
SECONDLOWVALUE is 0 and the SECONDHIGHVALUE is 1000, the result is
1000 minus 0, or 1000.
2. The number of values between the begin-range value and the total number of
values in the range is divided by the total number of values in the range. Consider
this example:
WHERE A >= 100
AND A <= 200
The result is (1000-100)/1000 or .90.
3. Then the number of values between the end-range value and the total number of
values in the range is divided by the total number of values in the range. The result
for the same example is (1000-200)/1000 or .20.
4. Instead of multiplying the two results, as is done when no range is used, the two
results are added together. In the example, the result is .90 + .20 or 1.10.
5. Selectivity is calculated by subtracting 1.00 from the result. In this case, the
selectivity is 1.10 - 1.00 or 10 percent selectivity.
Selectivity of Multivalued Predicates
For a multivalued predicate, SQL uses the formulas in Table 5-1 to determine
selectivity. These formulas are applied recursively so that all comparisons participate in
the result.
SQL calculates selectivity using all columns in the primary key and indexes according
to the rules for AND and OR noted in the preceding subsection.
Beginning with version 3.0, if the operator is =, then all columns of the multivalued
predicate are taken into account for selectivity. Otherwise, only the first pair of
comparison values is used for the calculation.
Table 5-1. Selectivity Formulas for Multivalued Predicates
Predicate
Form Formula
a,b < c,d The selectivity of (a < c) plus the selectivity of (a = c AND b < d)
a,b <= c,d The selectivity of (a < c) plus the selectivity of (a = c AND b <= d)
a,b >= c,d The selectivity of (a > c) plus the selectivity of (a = c AND b >= d)
a,b > c,d The selectivity of (a > c) plus the selectivity of (a = c AND b > d)