SQL/MX 2.x Reference Manual (H06.04+)

MXCI Commands
HP NonStop SQL/MX Reference Manual—540440-003
4-51
Considerations for SET SHOWSHAPE
Considerations for SET SHOWSHAPE
Default Control Query Shape
For those statements that do not have a shape—for example, the CREATE SCHEMA
statement—a control query shape (CQS) of the form CONTROL QUERY SHAPE
ANYTHING is issued.
CONTROL QUERY SHAPE ANYTHING resets the effect of any preceding CQSs. Its
use is especially important when CQSs are being generated from an input file of
commands and statements.
Examples of SET SHOWSHAPE
•
To turn on the display of access plans, enter:
SET SHOWSHAPE ON;
SELECT * FROM persnl.job
WHERE jobcode >= 500;
control query shape partition_access(
scan('JOB', forward, blocks_per_access 1, mdam off));
Job/Code Job Description
-------- ------------------
500 ACCOUNTANT
600 ADMINISTRATOR
900 SECRETARY
--- 3 row(s) selected.
•
To turn on the display of access plans, enter:
SET SHOWSHAPE ON;
SELECT * FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPTNUM = DEPT.DEPTNUM
AND EMPLOYEE.LAST_NAME = 'SMITH';
control query shape merge_join(sort(
partition_access(scan('EMPLOYEE', forward,
blocks_per_access 1, mdam off))),
partition_access(scan('DEPT', forward,
blocks_per_access 3, mdam off)));
Employee/Number First Name Last Name Dept/Num ...
--------------- ------------ -------------- -------- ...
89 PETER SMITH 3300 ...
--- 1 row(s) selected.
Use this displayed plan to implement a forced plan. For more information about
forcing plans, see CONTROL QUERY SHAPE Statement on page 2-35.