SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-12
Automatic Recompilation
basis. At run time, these statements are automatically recompiled when the compiler
directives allow this recompilation.
The quality of the query execution plans depends on the accuracy of the statistics used
by the compiler when determining the plan. If the statistics are not current, compilation
can cause a valid program, but the chosen query execution plan could give less than
the best performance. For the best performance for programs, you must ensure that
the statistics represent the current state of the table and indexes with reasonable
accuracy.
If you want to review the chosen query execution plans, you can SQL compile the
program and use the EXPLAIN option. The EXPLAIN utility reports the access paths
for each DML statement and, optionally, the DEFINEs in effect. For additional
information about the SQL compiler EXPLAIN utility, see the SQL/MP Reference
Manual.
Automatic Recompilation and Query Execution Plans
For automatic recompilation of programs or statements or for SQLCI queries or
dynamic SQL queries, the SQL compiler tries to generate the best query execution
plan that provides the best performance. If some of the required information is not
available, the compiler tries to generate the best available query execution plan using
the available objects.
These are points to consider about the best available query execution plan:
•
The best available query execution plan is the optimal access plan if all objects are
available. This best query execution plan typically results from automatic
recompilations because of these conditions:
°
The program was previously explicitly compiled, but statements were left
uncompiled because objects were not available.
°
The program was automatically recompiled because DDL operations on
referenced tables invalidated the program.
•
The best available query execution plan generated by automatic recompilation is
typically not the optimal plan when objects required for the previously compiled
best query execution plan are not available. In such a case, the best available
query execution plan can produce suboptimal performance. The plan, however,
ensures access to the data.
For automatic recompilation, the file label of the named or underlying table and the
catalog in which the named object is registered must be available. For a partitioned
table, only the file label of the specified partition must be available.
For statement execution, any partition of the named object can be opened. Other
partitions must be available only if the query requests data from another partition and
the SKIP UNAVAILABLE PARTITION option is not in effect.
For the execution of an INSERT or DELETE statement, all affected partitions of the
table and corresponding partitions of all indexes must be available. For the execution