SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-9
Use of Default Selectivity Values
Note that the optimizer combines both EMP_START predicates to obtain a selectivity
of .111. Finally, the optimizer uses the selectivity in steps:
.0100
* .0100
* .3333
* .3333
.0000111 = index selectivity
* .9900
* .3333
.00000367 = table selectivity
Use of Default Selectivity Values
The optimizer uses default selectivity values when
•
Statistics are not available because an UPDATE STATISTICS has not been
performed.
•
Statistics are not available because the table was empty when UPDATE
STATISTICS was last done.
•
A predicate involving a >, >=, <, or <= either
°
Cannot be used as a key; for example, a + 1 < 5 is not the same as a < 4
°
Compares a column with a host variable or parameter
If, for example, the value specified in a predicate is a host variable (as in a COBOL
program), SQL cannot compute the selectivity because the value of the host variable is
not known until run time.
In such cases, SQL operates as if an arbitrarily chosen default selectivity is in effect.
The default values depend on the type of predicate. Table 5-2 lists both computed
values and default values for different types of predicates.
Table 5-2. Computed and Default Selectivity Values for Predicates (page 1 of 2)
Type of Predicate Computed Value
Default
Selectivity
Equals (=) 1 / UNIQUEENTRYCOUNT 1 percent
Not equals (<>) 1 − (1 / UNIQUEENTRYCOUNT) 99 percent
Greater than (>) range / (SECONDHIGHVALUE −
SECONDLOWVALUE)
33 percent
Greater than or equal to (>=) range / (SECONDHIGHVALUE −
SECONDLOWVALUE)
33 percent
Less than (<) range / (SECONDHIGHVALUE −
SECONDLOWVALUE)
33 percent
Less than or equal to (<=) range / (SECONDHIGHVALUE −
SECONDLOWVALUE)
33 percent