SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-86
Considerations—CONTROL TABLE
to table A in the final directive overrides the specific references in the preceding
directives:
CONTROL TABLE A AS B ACCESS PATH INDEX INDEX2;
CONTROL TABLE A AS C ACCESS PATH INDEX INDEX3;
CONTROL TABLE A ACCESS PATH INDEX INDEX1;
In a host language program, specific scoping rules might apply to the use of the
CONTROL TABLE statement. For more information, see the SQL/MP
programming manual for your host language.

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:

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.
Make sure you are familiar with details of query operations as described in the
SQL/MP Query Guide, and be sure to restore default values for these options
immediately afterwards. For example:
CONTROL TABLE * ACCESS PATH SYSTEM;
CONTROL TABLE * JOIN METHOD SYSTEM JOIN SEQUENCE SYSTEM;
Also, be aware of these special considerations:

If you specify an access path, a query does not run unless that access path is
available. To allow for alternate paths, code your application to check for errors
and specify an alternate path if the normally-preferred path is not available.
(SQL automatically considers alternate access paths if you do not specify an
access path.)

Certain errors in the specification of ACCESS PATH, JOIN METHOD, or JOIN
SEQUENCE cannot be detected and reported until an affected DML query is
compiled. For example, if you erroneously specify a JOIN SEQUENCE greater
than the number of tables in the next SELECT that includes the table, an error
occurs in response to the statement that contains the SELECT, not to the
CONTROL TABLE directive.
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