SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)

Forcing Execution Plans
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-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.
Shapes and Sequence Generators
You can write shapes for queries accessing sequence generator values. For example,
following shape can be applied to access the next value of a sequence.
create table t1 (c1 int, c2 int);
create sequence seq1 int;
select seq1.nextval, c1 from t1 where c2 > 20;
control query shape nvf(partition_access(scan(path
'CAT.SCH1.T1', forward, blocks_per_access 1 , mdam
off)),esp_exchange(sg(partition_access(update_unique))));
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 NonStop SQL/MP
If you forced plans in NonStop SQL/MP by using the CONTROL TABLE statement, you
will need to rewrite your plans by using CONTROL QUERY SHAPE, which provides
Note. These plans are internally generated and cannot be controlled by the user.
TP663851.fm Page 10 Wednesday, January 30, 2013 5:37 PM