SQL Programming Manual for Pascal

Program Compilation and Execution
HP NonStop SQL Programming Manual for Pascal—528614-001
5-18
Using the EXPLAIN Utility
modifying a query would improve the performance of your program. The EXPLAIN
utility has these report options:
•
EXPLAIN PLAN report
•
EXPLAIN DEFINES report
Optimized execution plan:EXPLAIN PLAN report;
Generating an EXPLAIN Report
The EXPLAIN PLAN report applies only to SQL DML statements. A plan shows the
strategy for executing a DML statement and includes optimized access paths, joins,
and sorts.
The EXPLAIN PLAN report generates a plan for a statement that contains subqueries
into separate query plans, one for each subquery and one for the statement itself. This
report numbers the query plans in each statement in the order they appear. Each plan
can contain these steps:
1. Scan a table
2. Join two or more tables
3. Insert into a table
4. Perform a sort operation
Examples
In the next example, the SQL compiler compiles the NEWPROG program file using the
EXPLAIN PLAN option to generate the statement execution plans. The command also
specifies the catalog name rather than use the current default subvolume name for the
catalog. The SQL compiler writes the output to the spooler. The command is:
SQLCOMP / IN NEWPROG, OUT $S.#EXPLAIN, NOWAIT / &
... CATALOG SALES, EXPLAIN PLAN
By default, the SQL compiler uses the current set of DEFINEs to create the object file.
In the next example, the SQL compiler writes an execution plan and set of DEFINEs to
the EDIT file EXOUT, but it does not recompile the OXPLAIN program file:
SQLCOMP /IN OXPLAIN, OUT EXOUT/ NOOBJECT STOREDDEFINES &
EXPLAIN PLAN DEFINES, OBEYFORM
The catalog name for the program unit is stored in the program file so that you do not
have to include the CATALOG option. NOOBJECT suppresses the generation of a new
object file, so the compiler does not write a record about the program file to the
catalog.
The DEFINE set is the set stored with the program OXPLAIN. The execution plans are
the plans that the compiler would generate if it were generating object code; they are
not the plans stored in the object file OXPLAIN.