SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-93
Examples—CONTROL TABLE
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;

This 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 path, join
sequence, and join method for future SELECT operations to SQL. The example
uses table EMPLOYEE with a primary key of EMP_NO and an alternate index,
IEMP, on column DEPT_NO.
CONTROL TABLE EMPLOYEE AS E1 ACCESS PATH INDEX IEMP
JOIN SEQUENCE 1;
CONTROL TABLE EMPLOYEE AS E2 ACCESS PATH PRIMARY
JOIN SEQUENCE 2
JOIN METHOD NESTED;
SELECT E1.EMP_NAME, E1.DEPT_NAME, E2.EMP_NAME, E2.DEPT_NAME
FROM EMPLOYEE E1, EMPLOYEE E2
WHERE E1.DEPT_NO = "9999" AND E1.MGR_ENO = E2.EMP_NO;
CONTROL TABLE * ACCESS PATH SYSTEM
JOIN SEQUENCE SYSTEM
JOIN METHOD SYSTEM;
The CONTROL TABLE directives that precede the SELECT force SQL to use two
different access paths to access two different instances of table EMPLOYEE
(IEMP for E1, and primary access for E2). The statements also force SQL to use a
specified join order for processing the data, and to use a nested join method to join
the second table (E2) with the first (E1).

This example forces MDAM with 3 key columns for table T1:
CONTROL TABLE T1 ACCESS PATH PRIMARY MDAM ON USE 3 COLUMNS;

This example cancels all previously set CONTROL TABLE options and uses only
the default values for the options:
CONTROL TABLE *;