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

Forcing Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
5-10
Forcing Shapes on Views
logical JOIN specification, with SCAN(EMPLOYEE) as the second argument of the
join. The optimizer is free to choose nested, merge, or hash join as the implementation.
When you force a plan by using the physical operator SHORTCUT_GROUPBY, the
SHORTCUT_SCALAR_AGGR operator appears in the EXPLAIN output. If the
optimizer cannot produce a plan with SHORTCUT_SCALAR_AGGR, no plan is
returned.
Forcing Shapes on Views
When you prepare a query and use the EXPLAIN output to look at compiled statistics,
the output shows that the compiler expands the view and that the operation occurs on
underlying base tables. To affect a new shape on a SQL view, reference the underlying
base tables in your CONTROL QUERY SHAPE statement.
What Happens if No Plan Is Returned?
If you try to shape a plan and the optimizer fails to return a plan, consider these
reasons:
•
The forced shape might be incompatible with the issued query. That is, the shape
has no match in the optimizer search space defined by the optimizer rules. For
example, a plan is not returned if you attempt to force a table scan shape on a two-
table join query.
•
The forced shape might be compatible with the query, but matching plans are
pruned by optimizer heuristics. In this case, you might try changing the default
value for the DATA_FLOW_OPTIMIZATION and the
CROSS_PRODUCT_CONTROL attributes to OFF and try the plan again to see if
the results are different.
Migrating Forced Shapes From SQL/MP
If you forced plans in SQL/MP by using the CONTROL TABLE statement, you will
need to rewrite your plans by using CONTROL QUERY SHAPE, which provides more
control. Note that, while CONTROL TABLE enables you to force a single operation,
CONTROL QUERY SHAPE requires that you force the entire tree structure.
Forcing Group By Operations to the Data Access Manager
The SQL compiler has two methods for performing grouping and aggregation.
•
Sort group by requires the child of the group by to be ordered on the group-by
column and thus preserves ordering. This method incurs additional sort operations
unless input is already sorted on the grouping columns.
Note. GROUP BY operators are sometimes used even when no GROUP BY clause is
specified. The SQL compiler inserts a GROUP BY operator into a SELECT DISTINCT query to
remove duplicate rows.