NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-88
Examples—CONTROL TABLE
•
The following example forces SQL to choose a specific access plan for a query that
accesses a view created as follows:
CREATE VIEW EMPDEPT AS
SELECT EMP_NAME, EMP_NO, E.DEPT_NO, DEPT_NAME, DEPT_LOCN
FROM EMPLOYEE E, DEPT D
WHERE E.DEPT_NO = D.DEPT_NO;
The CONTROL TABLE directives force a join sequence and access path for table
EMPLOYEE and SALARY, leaving SQL to choose the join sequence for table
DEPT. Because the query involves only three tables, the CONTROL TABLE
directives implicitly force DEPT to have a join sequence of 1.
CONTROL TABLE EMPDEPT AS ED BASETABLE E
ACCESS PATH INDEX IEMP
JOIN SEQUENCE 2;
CONTROL TABLE SALARY
ACCESS PATH PRIMARY
JOIN SEQUENCE 3;
SELECT * FROM EMPDEPT ED, SALARY SA
WHERE ED.EMP_NO = SA.EMP_NO
AND ED.DEPT_NAME = "Engineering";
The use of the correlation name ED for the view is not necessary but is included in
the first CONTROL TABLE directive to demonstrate that a correlation name can be
included for views. The qualification of “BASETABLE E” in the first CONTROL
TABLE directive is necessary, however: without it, the CONTROL TABLE options
would apply to both the EMPLOYEE and DEPT tables of the view EMPDEPT, and
an error would occur.
•
The following example uses CONTROL TABLE to specify locks. The first directive
requests a table lock on table CUSTOMER. The following statements lock and
unlock the nonaudited table DEPT, using CONTROL TABLE directives with the
LOCK and UNLOCK statements to make sure the compiler considers the locking
mode when selecting an access path for DEPT.
CONTROL TABLE SALES.CUSTOMER TABLELOCK ON;
LOCK TABLE PERSNL.DEPT IN EXCLUSIVE MODE;
CONTROL TABLE PERSNL.DEPT TABLELOCK ON;
...
UNLOCK TABLE PERSNL.DEPT;
CONTROL TABLE PERSNL.DEPT TABLELOCK ENABLE;
•
The following example shows how to force SQL to use a specific index, join order,
and join method for a single SELECT statement, then return control over access