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-18
Considerations for CONTROL QUERY SHAPE
This statement enables the optimizer to add exchange nodes:
CONTROL QUERY SHAPE IMPLICIT EXCHANGE
HYBRID_HASH_JOIN (
SCAN('DEPT'),
SCAN('EMP'),
TYPE2);
For syntax and more information, see the SQL/MX Reference Manual.
Considerations for CONTROL QUERY SHAPE
If you think that you might benefit from the use of one of the CONTROL QUERY
SHAPE options, check your application with and without forcing the plan by using
actual statistics from production data.
If you use one of the options, you might want to change the forced shape later for
reasons such as:

The query might not be able to use a more efficient index that might be created in
the future.

The query might not be able to benefit from future enhancements to SQL.

Changes to the database structure (such as dropping an index) can require
recompilation when the option is in use.
Therefore, make occurrences of CONTROL QUERY SHAPE easy to find and change
by using one or more of these alternatives:

Make sure the forced shape applies only to the statement and table intended. Turn
the forced shape off as soon as you are finished (CONTROL QUERY SHAPE
OFF).

Isolate this forced shape in its own section and perform it from the inline
application code.

Place all statements affected by the forced shape in separate modules, called as
services by other modules.
This example shows placing a statement affected by a forced shape into a
separate module. Assume that an application uses a set of statically precompiled
SQL statements that are in a module named ansiMXSmd.esql. In addition, part
of the contents of this module include a frequently used query named
primarykeysQ1:
...
/* SQL statement_name=primarykeysQ1 */
exec sql select ... from
:"hv_schemata_table" prototype 'N.SCH.SCHEMATA' sc,
:"hv_objects_table" prototype 'N.DSCH.OBJECTS' ob,
:"hv_cols_table" prototype 'N.DSCH.COLS' co,
:"hv_keycolusage_table" prototype 'N.DSCH.KEY_COL_USAGE' ky,
:"hv_tblconstraints_table" prototype 'N.DSCH.TBL_CONSTRAINTS' tc
where sc.SCHEMA_VERSION = :"hv_schema_version1"
TP663851.fm Page 18 Wednesday, January 30, 2013 5:37 PM