SQL/MP Query Guide

HP NonStop SQL/MP Query Guide—524488-003
5-1
5 Selectivity and Cost Estimates
NonStop SQL/MP uses selectivity and cost when choosing an execution plan. This
section describes these topics:
•
How the Optimizer Estimates Selectivity on page 5-1
•
Assigning Cost to a Query on page 5-11
•
Evaluating Cost Estimates on page 5-19
•
How the Optimizer Chooses an Execution Plan on page 5-19
•
Forcing Execution Plans on page 5-20
How the Optimizer Estimates Selectivity
Selectivity is an estimate of the number of rows in a table or an index that satisfy a
given search condition and is represented as a percentage of rows, from 0 to 100. It is
central to the selection of an access plan by the optimizer. It depends on column
statistics maintained in the SQL catalog and on the predicates specified for a given
query.
The efficiency of a given index is determined based on its selectivity. If the control
statement INTERACTIVE ACCESS ON is specified, however, the optimizer attempts to
use an index, if feasible, without considering selectivity. The control statement
indicates that only the first few rows of the result set are pertinent to the request.
The number of rows that are examined affects the number of messages that are
exchanged between the file system and disk processes to retrieve the data, the
number of input/output operations that are performed, and so on.
There are three levels of selectivity:
•
Predicate selectivity is the fraction of rows in a table that satisfy the predicate.
•
Table selectivity is the fraction of rows that satisfy all the predicates of a query.
•
Index selectivity is the fraction of index rows that must be examined in evaluating a
query.
Selectivity influences the optimizer’s choice of these:
•
Access path (base table, alternate index, or index only)
For example, if the restriction specified by a WHERE predicate does not result in a
low enough selectivity to justify alternate-index access, base-table access is
chosen instead. (For more information about access paths, see Optimizing the
Access Path on page 4-4.)