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

Forcing Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
5-8
Writing the Forced Shape Statement
Using the sample query, you can translate the operator tree into this text format:
ROOT(HYBRID_HASH_JOIN (HYBRID_HASH_JOIN
(PARTITION_ACCESS (FILE_SCAN_UNIQUE),
PARTITION_ACCESS(FILE_SCAN)),
PARTITION_ACCESS (FILE_SCAN_UNIQUE)))
To refine a CONTROL QUERY SHAPE statement, several conditions apply to the text
format:
•
Leave off the ROOT node.
•
Leave off EXPR nodes.
•
Write FILE_SCAN and FILE_SCAN_UNIQUE nodes as SCAN.
•
Replace SPLIT_TOP(PARTITION_ACCESS(...)) with SPLIT_TOP_PA(...).
•
If no syntax exists for an operation in an operator tree (for example,
SORT_SCALAR_AGGR or SHORTCUT_SCALAR_AGGR), use something similar,
such as SORT_GROUPBY or SHORTCUT_GROUPBY. For the valid operators,
check Section 7, Operators and Operator Groups.
The refined text format looks like this:
HYBRID_HASH_JOIN (HYBRID_HASH_JOIN
(PARTITION_ACCESS (SCAN),
PARTITION_ACCESS (SCAN)),
PARTITION_ACCESS (SCAN))
Writing the Forced Shape Statement
You use the CONTROL QUERY SHAPE statement to write the forced shape
statement, using the text format that you previously formulated, and to replace the
operators that you want to force. The CONTROL QUERY SHAPE statement is
described in the SQL/MX Reference Manual. To understand why a plan uses certain
operators, see Section 7, Operators and Operator Groups
.
In many cases, you arrive at the decision to force a plan through experimentation.
Guidelines that can help you formulate plans follow.
Scope of CONTROL QUERY SHAPE
The result of the execution of a CONTROL QUERY SHAPE statement stays in effect
until the end of the current MXCI session or until changed or turned off by another
CONTROL QUERY SHAPE statement. Executing the CONTROL QUERY SHAPE
statement does not affect the execution of CONTROL statements, the EXPLAIN and
DISPLAY_EXPLAIN commands, LOCK and UNLOCK statements, DDL, and
transaction statements.
Caution. Always turn off CONTROL QUERY SHAPE after you force a shape for a particular
query. Otherwise, when you try to compile another query, the compiler fails to find a plan that
matches the persisting forced shape.