SQL/MX 3.2 Query Guide (H06.25+, J06.14+)

Forcing Execution Plans
HP NonStop SQL/MX Release 3.2 Query Guide—663851-002
5-18
Considerations for CONTROL QUERY SHAPE
and (sc.SCHEMA_NAME = :"hv_param2" or trim(sc.SCHEMA_NAME)
LIKE :"hv_param3" ESCAPE '\')
and (ob.OBJECT_NAME = :"hv_param4" or trim(ob.OBJECT_NAME)
LIKE :"hv_param5" ESCAPE '\')
and sc.SCHEMA_UID = ob.SCHEMA_UID and ob.OBJECT_UID = tc.TABLE_UID
and tc.CONSTRAINT_TYPE = 'P'
and ob.OBJECT_UID = co.OBJECT_UID and tc.CONSTRAINT_UID =
ky.CONSTRAINT_UID and ky.COLUMN_NUMBER = co.COLUMN_NUMBER
FOR READ UNCOMMITTED ACCESS order by 1, 2, 3, 5 ;
...
Assume you want to force the primarykeysQ1 query to use a given plan. You
can extract this query from its original ansiMXSmd.esql module and place it into
its own separate module, for example, primarykeysQ1.esql:
...
exec sql control query shape sort(
hybrid_hash_join(
nested_join(
hybrid_hash_join(
nested_join(cut,cut),
cut),
cut),
cut));
/* 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"
and (sc.SCHEMA_NAME = :"hv_param2" or trim(sc.SCHEMA_NAME)
LIKE :"hv_param3" ESCAPE '\')
and (ob.OBJECT_NAME = :"hv_param4" or trim(ob.OBJECT_NAME)
LIKE :"hv_param5" ESCAPE '\')
and sc.SCHEMA_UID = ob.SCHEMA_UID and ob.OBJECT_UID = tc.TABLE_UID
and tc.CONSTRAINT_TYPE = 'P'
and ob.OBJECT_UID = co.OBJECT_UID and tc.CONSTRAINT_UID =
ky.CONSTRAINT_UID and ky.COLUMN_NUMBER = co.COLUMN_NUMBER
FOR READ UNCOMMITTED ACCESS order by 1, 2, 3, 5 ;
exec sql control query shape off;
...
By extracting the query from its original module and placing it into a separate
module, you assure:

The forced plan is applied only to the SQL compilation of the query.

You can easily find and maintain the query in case you need to change it again
to force a different plan.
TP663851.fm Page 18 Thursday, August 2, 2012 4:47 PM