SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-11
Automatic Recompilation
Following the explicit recompilation, you can stop and start the server class to ensure
that all running objects use the newly compiled program. These commands illustrate
this action:
= FREEZE SERVER SRV-SDB102
= STOP SERVER SRV-SDB102
= THAW SERVER SRV-SDB102
= START SERVER SRV-SDB102
For more information about DEFINEs see Using DEFINEs on page 10-30.
For information about execution-time name resolution and how it relates to PATHMON
DEFINES, see Deferring Name Resolution on page 10-13.
Explicit Compilation and Query Execution Plans
A query execution plan is an execution method, including the semantics and execution
characteristics, for a compiled SQL statement. The SQL compiler stores the execution
plan in the program file during explicit compilation, and the SQL executor uses the plan
to run the SQL statement at run time.
For queries, the SQL optimizer usually examines as many execution plans as there are
ways to access the data. The optimizer then chooses the plan it considers the most
efficient, based on the number of input-output operations and the use of processor
time. The plans include any indexes used and any sort operations performed to order
the data as requested. The EXPLAIN utility displays the query execution plan chosen
by the optimizer.
The SQL compiler tries to compile each DML statement with the best query execution
plan, which provides the best performance. The compiler can determine the best query
execution plan only when all the required information for the SQL objects referred to in
the statements is available and current.
Necessary information includes:
•
The catalogs of the referenced objects. These catalogs contain the description of
the objects. The referenced objects can be local or remote tables or views and
local or remote partitions of tables.
•
The statistics on the tables and associated indexes.
Both explicit compilation and automatic recompilation determine query execution plans
for DML statements. The types of query execution plans depend on the type of SQL
compilation.
Explicit SQL compilation requires all the necessary information to be available to
create the best query execution plan. If some of the required information is not
available, the compiler cannot generate a query execution plan for the affected
statements.
If the compilation is otherwise successful in generating an object file, the program is
registered and marked valid in the catalog; the statements for which the best query
execution plan could not be generated are marked invalid on a statement-by-statement