SQL/MX 2.x Query Guide (G06.24+, H06.03+)

HP NonStop SQL/MX Query Guide—523728-003
5-1
5 Forcing Execution Plans
Use the information in this section to make decisions about forcing query execution
plans.
•
Why Force a Plan? on page 5-1
•
Checklist for Forcing Plans on page 5-2
•
Displaying the Optimized Plan on page 5-2
•
Reviewing the Optimized Plan on page 5-3
•
Translating the Operator Tree to Text Format on page 5-5
•
Writing the Forced Shape Statement on page 5-8
The SQL/MX optimizer attempts to generate the most cost-efficient plan available. In
some situations, you might find that you want to vary the plan selected by the
optimizer.
When you force an execution plan, you are instructing the executor how you want the
plan to execute. You use the CONTROL QUERY SHAPE statement to force a
particular plan shape on a query. This statement is presented in SQL syntax as an
SQL/MX extension to the ANSI standard. You need to understand the internal query
tree structure to force a plan. You can place any allowed syntax within the CONTROL
QUERY SHAPE statement, but the syntax does not ensure that a valid plan will be
generated.
CONTROL QUERY SHAPE is very dependent on the internal design of the optimizer.
Future improvements to the optimizer might make it necessary for users to change
their CONTROL QUERY SHAPE statements.
For more information about the CONTROL QUERY SHAPE statement, see the
SQL/MX Reference Manual.
Why Force a Plan?
Some of the possible reasons for forcing a plan include:
•
Testing purposes. You might want to try different execution scenarios than those
provided by the optimizer.
•
The optimizer might not have found the optimal plan. This situation could occur
because of lack of recent statistics and calibration, data skew, or aggressive
pruning.
Caution. If you use the CONTROL QUERY SHAPE statement, you can override the
optimizer’s standard cost estimates and cause negative performance. In addition, if you try to
force an invalid plan, no plan will be returned to you. Use CONTROL QUERY SHAPE only if
the optimizer does not produce the optimal plan. You might want to contact your service
provider for assistance.