SQL/MP Query Guide

The Optimizer
HP NonStop SQL/MP Query Guide—524488-003
2-4
How the Optimizer Chooses an Execution Plan
•
Execution step at which a predicate or subquery should be evaluated
•
The sequence (join order) in which tables will be scanned
•
The join strategy—hash, sort merge, key-sequenced merge, or nested
•
Optimal sort strategy, by the following:
°
Examining the sort keys for any ORDER BY, GROUP BY, or DISTINCT
requests, and combining the sorts whenever possible
°
Eliminating sorts for ORDER BY, GROUP BY, or DISTINCT requests if the
chosen access path returns rows in the desired order
•
Optimal sort type: sorting in memory (UPS), using a sort process (SORTPROG) or
insertion sort
•
Whether sequential block buffering (SBB) will be used
•
If parallel execution is enabled, whether parallel or nonparallel execution is more
efficient
•
Whether table locking would be appropriate
These topics are described in Section 3, Improving Query Performance Through Query
Designand Section 4, Improving Query Performance With Environmental Options.
Two important aspects of query evaluation are selectivity and cost, both described in
Section 5, Selectivity and Cost Estimates
•
Selectivity is an estimate of the percentage of rows in a table or an index that
satisfy a search condition. Selectivity is represented as a percentage from 0 to
100.
•
Cost is an estimate of the amount of time the system takes to complete evaluation
of a specific query. Cost includes an estimate of consumption of these resources:
°
Number of I/Os
°
Number of sorts to be performed
°
Amount of data to be processed
°
Number of interprocess messages
°
CPU processing for searches, fetches, processing (joins, grouping), and
moving data between buffers
Cost is expressed in terms of the equivalent number of I/O operations that could be
performed in the same time.
SQL chooses the execution plan with the lowest estimated cost.