SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-6
Index Selectivity
NULL Values
The presence of NULL values in a column is considered when determining selectivity
of IS NULL predicates and range predicates and in determining index selectivity for
pairs of range predicates. For example, the selectivity of C IS NULL is 0 percent if
column C has the NOT NULL attribute
If null values are present and the UEC for a column is two or less, SQL sets
SECONDHIGHVALUE to the highest nonnull value in the table. If UEC is three or less,
UPDATE STATISTICS sets the SECONDLOWVALUE to the lowest value in the table.
The optimizer uses these statistics to determine whether SQL detected any null values
in the column.
If UEC is 3 or more, Non Stop SQL operates as if nulls are present unless the column
is defined with the NOT NULL attribute. If nulls are present, SQL operates as if the
nulls occur in equal proportion with other values, and their frequency is estimated as
1 / UEC.
Index Selectivity
Index selectivity refers to the combined selectivity of the begin-key and end-key
predicates for an access path:
•
For primary-key access, index selectivity is the estimated percentage of rows in the
base table that will be examined in evaluating the query.
•
For alternate-index access, index selectivity is the estimated percentage of index
entries that will be examined.
•
For a table scan, begin keys and end keys are not applicable. All rows must be
examined, so index selectivity is always 100 percent.
MDAM predicates also affect selectivity. For related information on MDAM, see The
Effect of the MultiDimensional Access Method (MDAM) on Costs on page 5-17.
Another way to look at index selectivity is the number of rows actually accessed by the
disk process.
To examine a row, the row must be read from disk. Rows that are outside the bounds
set by the begin keys and end keys need not be read, however, so index selectivity
strongly affects the cost of a scan operation. A low index selectivity implies a low cost
estimate for the scan.
To determine index selectivity, the optimizer identifies index predicates for a specific
access path. The optimizer then combines the selectivities as if they are independent
of each other. Thus, the index selectivity is the combined selectivity of begin-key and
end-key predicates for the access path. (Note that this strategy differs from index
predicates, which are used in the calculation of table selectivity.)
The presence of NULL values in a column is considered when determining index
selectivity for pairs of range predicates; for more information, see the preceding
subsection “NULL Values on page 5-6.”