NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
E-15
Considerations—EXPLAIN
You can use the information in an EXPLAIN report to tune queries and to help
determine whether to add or drop indexes for a database. See the NonStop SQL/MP
Query Guide for listings of sample EXPLAIN reports and for a detailed explanation
of what to look for when you analyze EXPLAIN output.
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 DEFINE reports
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 NonStop SQL/MP
programming manual for the host language you use.
•
Execution plan report format
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 the
following 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.
The following 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.
Following the figure is an alphabetic list of the elements of the plan with an
explanation of each element.