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

Forcing Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
5-9
Shaping Portions of an Operator Tree
Use the CUT, ANYTHING, or OFF option to turn off the shape:
CONTROL QUERY SHAPE OFF;
Shaping Portions of an Operator Tree
You can use the ANYTHING option to partially shape an operator tree. Use this option
when you want a certain operation only and do not care how the rest of the plan is
optimized. If you specify a partial tree, ANYTHING marks the point where you want the
optimizer to “take over” and choose the best solution. This example shows the partial
shape of an operator tree:
CONTROL QUERY SHAPE join (anything, union (anything, scan));
Using Logical and Physical Specifications
You can use logical or physical operators with the CONTROL QUERY SHAPE
statement. Logical operators are relational operators that do not denote an
implementation. Examples include join, group by, and scan. Physical operators are
relational operators that specify the actual implementation or run-time algorithm, such
as merge join, hash group by, or file scan.
If you want to specify that an operation occurs, but you do not really care which
algorithm the optimizer chooses to implement, use the logical specification. For
example, you might want to specify that table EMPLOYEE is scanned as the last table
of a join, but you want the optimizer to choose the join algorithm. In that case, use the
Logical Operators Physical Operators
scan FILE_SCAN, INDEX_SCAN
hash_join HYBRID_HASH_JOIN,
ORDERED_HASH_JOIN
join NESTED_JOIN, MERGE_JOIN,
HYBRID_HASH_JOIN,
ORDERED_HASH_JOIN
groupby SORT_GROUPBY, HASH_GROUPBY,
SHORTCUT_GROUPBY
union UNION
sort SORT
exchange PARTITION_ACCESS (file system interface
for communicating with DAM)
REPARTITION (redistributes data)
SPLIT_TOP_PA (reads data from multiple
partitions in parallel, parallel version of
PARTITION_ACCESS node)
expr EXPR (internally generated node, not
necessary to specify)
tuple TUPLE