SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-10
Join Selectivity
For example, the selectivity of this predicate is 0.33:
RETAIL_PRICE > :host_variable
The selectivity of this predicate can be reasonably computed because the computation
does not depend on the supplied value:
RETAIL_PRICE = :host_variable
If the default selectivity differs very much from the actual selectivity, SQL might choose
an inefficient execution plan for the query. Therefore, you should periodically collect
statistics with the UPDATE STATISTICS command.
Join Selectivity
For joins, the effective UEC for join columns equals the UEC of each column times the
selectivity of predicates applied prior to joining. This calculation affects the choice of
join method; for example, if UEC equals 1, hash join is not chosen.
Grouping Selectivity
A grouping operation is performed whenever a GROUP BY is specified. The elements
of a GROUP BY list are either named columns or expressions. For each named
grouping column with statistics, the optimizer uses the UEC as follows:
•
Estimates the UEC prior to grouping.
•
If there are single-table predicates in a WHERE clause on the column, the
optimizer estimates the UEC as the product of the predicate selectivity times the
lowest selectivity value on the grouping column times the UEC computed by
UPDATE STATISTICS.
•
If there is a join predicate on the grouping columns, the optimizer uses the UEC
computed during plan generation.
LIKE N. A 10 percent
NOT LIKE N. A 30 percent
EXISTS N. A 40 percent
NOT EXISTS N. A 60 percent
IS NULL 0 (zero) percent for a column with
the NOT NULL attribute; otherwise,
1 / UNIQUEENTRYCOUNT
0 percent for a
column with the
NOT NULL
attribute;
otherwise, 1
percent
Table 5-2. Computed and Default Selectivity Values for Predicates (page 2 of 2)
Type of Predicate Computed Value
Default
Selectivity