SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-6
SQL Compilation and Recompilation
Logging might be especially helpful on a system where automatic recompilations are
not wanted for performance reasons. Examine any program that has a recompilation
logged to determine whether the program needs explicit recompilation. Use VERIFY to
check the program entry in the catalog.
Programs marked invalid in the catalog need explicit SQL compilation. A program or
statement could be logged for recompilation but might not be invalid, such as a
recompilation because of an unavailable node. Programs recompiled at run time but
not otherwise invalid do not need explicit compilation.
SQL Compilation and Recompilation
SQL compilation verifies the use of SQL objects, optimizes access paths to the
database for each SQL statement, and writes the object code for the plan to use the
chosen paths. Successful SQL compilation always generates an executable query
execution plan for each SQL statement in all interfaces, namely explicit SQL
compilation, automatic recompilation, dynamic SQL statements, and SQLCI ad hoc
queries (which are dynamic SQL statements).
The results of a compilation depend on whether the compilation is explicit or automatic
and on the SQL compiler options that are in effect.
Results also depend on statistics. SQL compilation uses statistics in the catalogs to
determine access paths, depending on the availability of the objects. Unavailable
objects, such as an index, affect the path chosen. For more information about
statistics, see Keeping Statistics Current on page 14-7.
This subsection describes features of SQL compilation, including explicit compilation
and automatic recompilation, SQL compiler options that control recompilation, and
query execution plans. For additional information about query execution plans, see the
SQL/MP Query Guide.
Explicit Compilation
Explicit compilation occurs when you run the SQL compiler, specifying a host-language
object program file. Explicit compilation also occurs for SQL programs stored in
Guardian files when you specify the SQLCOMPILE ON option for the RESTORE utility.
The results of successful explicit compilation are:
•
Executable object code is generated in the program file for the optimized access
paths.
•
The file label of the program file is marked with the SQL SENSITIVE and SQL
VALID flags being true (set on).