SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-9
Generating an EXPLAIN Plan
If a query has subqueries, the report shows additional steps in the same way for each
subquery.
You can use the information in the EXPLAIN plan for these types of tasks:
•
Assisting application program design; for example, the plan can help you
°
Determine the access path to be chosen.
°
Identify problems causing long response times, such as large sorts, full table
scans, and correlated subqueries.
•
Assisting database design; for example, you can use it to tune queries and to help
determine whether to add or drop indexes for a database.
•
Determining whether the optimizer chooses the expected plan and whether there
are ways to improve query performance. For example, you might decide that
creating an additional index can improve the performance of the query.
The EXPLAIN report is based on current information 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. If, for example,
you drop an index, the execution plan will be different than the plan that existed before
you dropped the index.
Reports generated through the SQLCOMP compiler option include a section that lists
each DEFINE used in an SQL statement. For more information on the compiler option,
see the SQL/MP Programming Manual for your host language. For detailed information
about each element in the report, see the SQL/MP Reference Manual.
Generating an EXPLAIN Plan
You can invoke EXPLAIN in one of two ways:
•
As an SQLCI command
•
As an option of the SQL compiler
This subsection describes how to invoke EXPLAIN as an SQLCI command. The
general syntax is:
EXPLAIN [ PLAN FOR ] { statement }
{ statement-name }
PLAN FOR is an optional clause that does not affect output. statement is an SQL
DML statement, by itself or enclosed in single or double quotation marks. statement-
name is the name of a prepared SQL statement.
This example prepares the statement first and then specifies EXPLAIN:
Note. This manual supports NonStop SQL/MP D30.02 and D30.03. Cost estimates reported
by the EXPLAIN utility are not considered to be comparable with cost estimates from previous
versions; the cost for the same query might differ from release to release.