SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-43
Considerations for CONTROL QUERY SHAPE
ALL (density,...)
forces an enumeration algorithm to be used by MDAM for each key column
as determined by the density list. All the remaining columns are used by
MDAM, and their enumeration algorithms are determined by the system.
For more information, see the SQL/MX Query Guide.
Considerations for CONTROL QUERY SHAPE
Scope of CONTROL QUERY SHAPE
The result of the execution of a CONTROL QUERY SHAPE statement stays in effect
until the end of the current MXCI session or until the execution of another CONTROL
QUERY SHAPE statement overrides it.
If you do not execute CONTROL QUERY SHAPE OFF immediately after the execution
of the query with the forced plan, the next query might not fit the forced plan in effect
and will return an optimizer error and not compile. The error returned by NonStop
SQL/MX has the form:
*** ERROR[2105] This query could not be compiled because of
incompatible Control Query Shape (CQS) specifications.
Inspect the CQS in effect.
*** ERROR[8822] The statement was not prepared.
The result of the execution of a CONTROL QUERY SHAPE statement does not affect
the execution of CONTROL statements, the LOCK and UNLOCK statements, and
transaction statements.
Examples of CONTROL QUERY SHAPE
•
Switch OFF the forced plan for the current MXCI session:
CONTROL QUERY SHAPE OFF;
•
Use CUT to shape a partial operator tree:
CONTROL QUERY SHAPE JOIN (CUT,UNION(CUT,SCAN));
•
This example shapes the EXPLAIN operator tree by using the CONTROL QUERY
SHAPE statement. Suppose that you have this query:
SELECT * FROM employee, dept
WHERE employee.deptnum = dept.deptnum
AND employee.last_name = 'SMITH';
Employee/Number First Name Last Name Dept/Num ...
--------------- ------------ -------------- -------- ...
89 PETER SMITH 3300 ...
--- 1 row(s) selected.