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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-44
Examples of CONTROL QUERY SHAPE
By using the SET SHOWSHAPE command, you can inspect the default plan
generated by the optimizer:
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)));
Instead of using the default MERGE_JOIN and SORT for this query, you can
shape the EXPLAIN operator tree by using this NESTED_JOIN replacement:
CONTROL QUERY SHAPE
NESTED_JOIN (PARTITION_ACCESS(SCAN),
PARTITION_ACCESS(SCAN('DEPT')));
SET SHOWSHAPE ON;
SELECT * FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.DEPTNUM = DEPT.DEPTNUM
AND EMPLOYEE.LAST_NAME = 'SMITH';
control query shape nested_join(
partition_access(scan('EMPLOYEE', forward,
blocks_per_access 1, mdam off)),
partition_access(scan('DEPT', forward,
blocks_per_access 1 , mdam off)));
Employee/Number First Name Last Name Dept/Num ...
--------------- ------------ -------------- -------- ...
89 PETER SMITH 3300 ...
--- 1 row(s) selected.
The second CONTROL QUERY SHAPE statement is displayed by the SET
SHOWSHAPE ON statement. Notice that, because you specified DEPT, you do not
have to specify EMPLOYEE. The system uses the other table in the join as the
default table name.
•
Suppose that you have a table T1 consisting of columns A, B, C, D, E, and F with a
primary key defined as columns A and B. Suppose further that an index IT1 is
defined as columns C, D, E, and F of table T1. These examples illustrate some of
the scan options you can specify for table T1:
°
Scan table T1. You want the system to choose whether to use an index or
base table, in addition to the other scan options.
SCAN (TABLE 'T1')