SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-11
Assigning Cost to a Query
The number of groups is then the product of the UECs of each grouping column,
bounded by the number of rows being grouped.
Default selectivity is used when statistics are not available. If the grouping element is
an expression, the element’s selectivity is based on the underlying columns in the
expression.
Assigning Cost to a Query
Cost is an estimate of the amount of time the system takes to complete evaluation of a
specific query. The optimizer uses cost as a factor in selecting an optimal access plan.
Even though cost is an estimate and is not an exact measure, it is very useful for
comparing the relative efficiency of different execution plans for a given query.
Cost has many components, including the number of physical I/O operations to
perform, the number of instructions to execute, the number of sorts to perform, and the
number of messages (local and remote), and the amount of data to be transferred
between processes. The optimizer does not take into account the types of processors,
disks, or communication lines when it calculates cost. So the cost of a query will not
change if you use faster processors, disks, or communication lines.
To facilitate the computation of cost, all components of cost are expressed in the
number of physical I/Os. The optimizer expresses cost in the equivalent number of
physical I/Os that must be issued to complete the query.
Cost is only an estimate because there are many variables the optimizer does not have
access to at compile time—or that SQL cannot control—such as:
•
The type of processor can change at run time.
•
The load of the system cannot be predicted for the time the query is executed.
•
The elapsed time for the completion of a query varies depending a variety of
runtime factors.
•
The values of host variables cannot be predicted.
•
The size and availability of cache can change. (For information about the effect of
cache on query performance, see Section 4, Improving Query Performance With
Environmental Options.)
In the final phase of query plan selection, the optimizer chooses the plan with the
smallest numeric cost. To display this cost, use DISPLAY STATISTICS or EXPLAIN,
described in Section 6, Analyzing Query Performance.
The following subsections describe how SQL calculates cost for various types of query
operations.