SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Reviewing Query Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
4-6
Extracting EXPLAIN Output From Embedded SQL
Programs
Four nodes (operators) appear in the plan: FILE_SCAN, PARTITION_ACCESS,
SORT, and ROOT. Operators are listed under Section 7, Operators and Operator
Groups.
This query requires a full table scan (FILE_SCAN), because the query selected all
employees, and required a SORT, because employee names are ordered by salary.
Four rows have been selected. The EXPLAIN function returns one row for each
operator used to evaluate the query.
Note that the description field contains different information for each operator. The
description for the FILE_SCAN node indicates the scan type and file name of the input,
the begin and end keys, the read direction, and other unspecified file access
information. The description for the SORT node indicates the key for sorting. The
description for the ROOT node lists all the columns in the SELECT statement list, the
ordering by column for the output, and the SQL statement. For full details of the
description column for each operator, see Section 7, Operators and Operator Groups.
Extracting EXPLAIN Output From Embedded SQL Programs
You can easily extract the EXPLAIN output from embedded SQL programs. You must
supply the module name, which you can supply in the program itself with a MODULE
clause, or you can check for the name in the module definition file. For example,
suppose that the module name of your embedded program is
MYCAT.MYSCH.MYMOD.
If your program contains multiple statements, this query explains all statements in the
order that they appear in your module:
SELECT * FROM TABLE (EXPLAIN('MYCAT.MYSCH.MYMOD', '%'));
You can review the EXPLAIN output any time after you have compiled the program,
and you will see the plan that was actually chosen at compile time. Note that the
second argument to the EXPLAIN is a LIKE pattern that is used on the statement-
name column.
Using DISPLAY_EXPLAIN to Review the Execution Plan
The next example of the DISPLAY_EXPLAIN command shows the information in the
execution plan for a query that uses predicates. DISPLAY_EXPLAIN displays all the
columns for the execution plan.
>>DISPLAY_EXPLAIN
+>SELECT last_name, first_name, salary
+>FROM employee WHERE
+>salary > 40000.00 AND jobcode=450;
When you use the DISPLAY_EXPLAIN command, you must page down to see the
entire output:
MODULE_NAME
STATEMENT_NAME