SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-20
Forcing Execution Plans
•
A table or index with a lower estimated cost
In general, the optimizer attempts to choose a local table or index that has the least
number of qualifying rows that must be examined.
Forcing Execution Plans
The goal of the optimizer is to generate a plan that works well on the average.
Because of variations in applications and data, however, SQL sometimes chooses a
plan that is not optimal. In such cases, you can specify a CONTROL TABLE option that
forces the optimizer to choose these (listed with examples showing when you might
use each option):
•
Access path for a table (ACCESS PATH), if CONTROL TABLE INTERACTIVE
ACCESS ON did not cause the optimizer to choose a specific index
•
Join sequence when processing a query (JOIN SEQUENCE), if ORDER BY or
other specifications did not influence the join sequence
•
Join methods when processing a query (JOIN METHOD), if the Measure product
has been run against different executions of the query and consistently indicates
that a certain join method would perform better than the one chosen by the
optimizer
Note that while CONTROL QUERY and CONTROL EXECUTOR directives
recommend actions, the CONTROL TABLE directive is treated as a specific request.
Situations that might benefit from these CONTROL TABLE options include:
•
Nonuniform data distribution; the optimizer operates as if distinct values are
uniformly distributed over ranges of values
•
Predicates that are not independent of one another; the optimizer operates as if
predicates are independent.
•
Selectivity estimates, being probabilistic, do not reflect the actual runtime
environment.
If you suspect that you might benefit from the use of one of these options, check your
application with and without the CONTROL option, using actual Measure statistics from
production data.
If you use one of the options, you might want to change this directive later for reasons
such as:
•
The query might not be able to use a more efficient index that might be created in
the future
Caution. These CONTROL TABLE options override the optimizer’s standard cost estimates
and therefore might cause performance degradation instead of enhancement. Use of these
options requires a thorough understanding of the optimizer. Use these options only if the
optimizer does not produce the optimal plan.