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

Forcing Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
5-2
Checklist for Forcing Plans
In these situations, forcing a plan gives you the power to control the plan shape. The
optimizer chooses the optimal plan that matches the forced shape.
Checklist for Forcing Plans
Before you can force a plan, you need to know the contents of the plan:
1. Display the optimized plan for a prepared statement with the EXPLAIN function.
2. Review the optimized plan and costs associated with the operations.
3. Translate the operator tree into a text format by using the SHOWSHAPE utility or
the format rules.
4. Use the CONTROL QUERY SHAPE statement to reshape the operator tree based
on the text format that you specify.
Each of these steps is described in greater detail in the next subsections.
Displaying the Optimized Plan
Follow the steps in Section 4, Reviewing Query Execution Plans to display the query
execution plan and to understand the operator tree provided by the optimizer. Note that
you can view the optimized plan in several formats:
•
Use the EXPLAIN function to display portions of the optimized plan.
•
Use the DISPLAY_EXPLAIN function to view the entire optimized plan.
•
Use the Visual Query Planner (VQP) to graphically display the entire optimized
plan.
This sample query is used throughout this section to show how you can force an
execution plan:
SELECT EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, DEPT.MANAGER,
EMPLOYEE.DEPTNUM, JOB.JOBCODE
FROM DEPT, EMPLOYEE, JOB
WHERE DEPT.DEPTNUM=3100 AND EMPLOYEE.DEPTNUM=3100 AND
JOB.JOBCODE=300;
Note. To avoid unexpected results, turn off query caching at the beginning of your VQP
session.