SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
E-14
Considerations—EXPLAIN
EXPLAIN reports and for a detailed explanation of what to look for when you
analyze EXPLAIN output, see the SQL/7MP Query Guide.
Note that an EXPLAIN report is based on information available at the time you
generate the report. If access paths or statistics change before you execute a
query for which you obtained an EXPLAIN report, SQL might use a different
execution plan. For example, the EXPLAIN execution plan for a SELECT
statement reflects the access paths that exist at the time. If you use EXPLAIN to
generate an execution plan for the same statement after dropping an index used in
the original plan, the new plan will be different.

EXPLAIN reports generated through the SQLCOMP command rather than through
SQLCI include an optional section that lists each DEFINE used in an SQL
statement within the program and the Guardian name associated with that DEFINE
at compilation time. This portion of the report is generated in the form of ADD
DEFINE commands or INFO DEFINE output, depending upon the option you
select.
For more information, including samples of such reports, see the SQL/MP
programming manual for the host language you use.

The execution plan for each DML statement described in an EXPLAIN report is
divided into one or more steps: one for a scan of each table in the FROM clause
and one for each union operator in the query. Each step involves one or more of
these operations:

Scan of a table

Join of two or more tables

Insert into a table

Sort operation

Hash operation
The plan shows different types of information for each type of operation. For joins,
for example, the plan shows the order of joining, join methods, and sorting
operations.
Predicate information in an execution plan can contain multibyte characters. If
multibyte characters are present, those characters might not be displayable on
your output device.
This example shows the format of an execution plan. Optional lines and clauses in
the report are shown in brackets or braces, as for syntax notation. This figure is an
alphabetic list of the elements of the plan with an explanation of each element.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan <n> [ : Will utilize parallel execution ]
SQL request : { Delete | Update | Select | Insert |
Insert-Select | Union of Selects }
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-----------------------------------------------------------
Plan step n [: Perform an [ [Inner|Left] Join|Union ] ]
[ Join strategy : [Nested | [Hybrid] Hash | ]