SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-16
Reviewing Sample EXPLAIN Plans
Options, provides a more thorough description of when parallel execution is and is not
chosen.)
If you are expecting parallel execution and it is not chosen, you might not have enabled
parallel execution. You must enable parallel execution by specifying CONTROL
EXECUTOR PARALLEL EXECUTION ON before SQL attempts to process a query or
parts of a query in parallel.
Reviewing Sample EXPLAIN Plans
The following subsections show several examples of EXPLAIN plans. The descriptions
of the first two examples include a detailed analysis of the entire EXPLAIN plan.
To avoid needless repetition, the remaining examples, describe only specific aspects of
each EXPLAIN plan—details that were not seen in previous plans. For example, the
descriptions of the EXPLAIN plans for subqueries emphasize only those details related
to subquery evaluation.
Scan through all the examples to get a thorough knowledge of the type of information
you can discern from an EXPLAIN plan. Also, see the SQL/MP Reference Manual,
which provides an alphabetic description of each element of an EXPLAIN plan.
When scanning the following examples, keep in mind that an EXPLAIN plan can
consist of several plan steps. The steps describe such things as scans of tables,
UNION operations, and so on. Plan steps are not necessarily executed according to
how they are numbered. For example, if the query is a noncorrelated subquery, then
plan step 2, the evaluation of the subquery, evaluates once before plan step 1, the
evaluation of the outer query.
Each plan step might consist of one or more operations, such as scans or sorts. Each
operation is assigned a separate cost. Each query is assigned a total cost.
EXPLAIN Plan for Simple SELECT
This example shows an EXPLAIN plan for a simple SELECT statement on a single
table.
The query retrieves all rows from the EMPLOYEE table. The primary key is EMPNUM.
The query follows:
EXPLAIN SELECT * FROM EMPLOYEE ;
The total cost of the query is 2.
Example 6-7 on page 6-17 shows the EXPLAIN plan for the query. The plan consists of
one step with one operation. A detailed analysis follows.