NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-81
Considerations—CONTROL TABLE
that table or view. It also does not affect the values of options set with different
specific references to that table or view.
For example, the following three directives can coexist if entered in the order
shown:
CONTROL TABLE A ACCESS PATH INDEX1;
CONTROL TABLE A AS B ACCESS PATH INDEX2;
CONTROL TABLE A AS C ACCESS PATH INDEX3;
Accesses to table A from references that use correlation names B or C use INDEX2
or INDEX3, respectively, but other accesses to table A use INDEX1.
The order of the directives just shown is significant because a CONTROL TABLE
directive with at least one option that makes a general reference to a table or view
overrides more specific directives that were entered previously. For example, the
following sequence of directives causes all accesses to table A to use INDEX1 (even
those from references that use correlation names B or C), because the general
reference to table A in the final directive overrides the specific references in the
preceding directives:
CONTROL TABLE A AS B ACCESS PATH INDEX2;
CONTROL TABLE A AS C ACCESS PATH INDEX3;
CONTROL TABLE A ACCESS PATH INDEX1;
In a host language program, specific scoping rules might apply to the use of the
CONTROL TABLE statement. For more information, see the NonStop SQL/MP
programming manual for your host language.
•
Clearing CONTROL TABLE options
If you specify the CONTROL TABLE directive without options, all previously
specified options for the table or view referenced exactly as specified in the
directive revert to their default values.
In this case, a general reference to a single table does not affect more specific
references to that table:
•
Controlling access paths and joins
Normally, you should allow SQL to determine access paths, join methods, and join
sequences. SQL does this automatically (basing its choices on stored statistics,
assumptions about data distribution, and availability of access paths) unless you use
the ACCESS PATH, JOIN METHOD, or JOIN SEQUENCE options in the
CONTROL TABLE directive.
Use these options only in specific situations in which you know that SQL's current
algorithm does not produce optimal results.
CONTROL TABLE A; Clear all options set for table A, but not options set for table A
AS B
CONTROL TABLE *; Clear all options set for all tables