SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-3
Predicate Selectivity
Predicate Selectivity
Predicate selectivity is the estimated percentage of rows in a table or an index that
satisfy a given predicate. The optimizer uses the selectivity of key predicates to
estimate the number of rows to examine; it uses the selectivity of the rest of the
predicates to estimate the number of rows that qualify. Those rows that actually satisfy
the predicate are selected for processing at the next stage.
Predicate selectivity is a number that expresses the effectiveness of a predicate as a
filter. The number is a probabilistic estimate.
For example, suppose that there are 100 items, numbered from 1 to 100, in the
INVNTRY table. The selectivity of this predicate is 0.9 or 90 percent, because 90 out of
100 rows satisfy the condition specified by the predicate:
ITEM_NO > 10
Alternately, if the selectivity of the predicate (SALARY < 50000) equals .6667, then the
optimizer has estimated that 66.67 percent of the rows in the table contain a value less
than 50,000 in the column SALARY.
In general, these rules apply:
•
Transformations are applied according to the rules listed in How the Optimizer
Processes Predicates on page 3-4.
•
For predicates of the form column = constant, the selectivity of the predicate is:
1 / (UNIQUEENTRYCOUNT)
•
Reversing a predicate does not affect its selectivity. For example, these predicates
have identical selectivities:
X >= Y
Y <= X
Numeric Range Predicates
Predicates with one of these forms are called exact numeric range predicates:
column < constant
column <= constant
column > constant
column >= constant
The optimizer distinguishes between < and <=, and > and >=, as follows:
•
column < is treated as column < constant
•
column <= constant is treated as column < constant + LSB; the least
significant bit of the constant is incremented by 1.
•
column > constant is treated as NOT column < constant + LSB; the least
significant bit of the constant is incremented by 1.