SQL/MX 2.x Query Guide (H06.04+, J06.03+)

ManualsBrandsHP ManualsServerHP Integrity NonStop H-Series

Accessing SQL/MX Data

HP NonStop SQL/MX Query Guide—540437-005

2-12

Understanding Unexpected Access Paths

•

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"

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(