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

Reviewing Query Execution Plans
HP NonStop SQL/MX Query Guide—523728-003
4-2
Using the DISPLAY_EXPLAIN Shortcut
1. In MXCI, prepare the query:
PREPARE FINDSAL FROM
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEE
WHERE SALARY > 40000.00;
2. Display selected rows of the execution plan for the prepared statement FINDSAL
by using the EXPLAIN function:
SELECT SEQ_NUM, OPERATOR, TOTAL_COST
FROM TABLE (EXPLAIN (NULL, 'FINDSAL'));
Output results from the query are:
SEQ_NUM OPERATOR TOTAL_COST
----------- ---------------------------- --------------
1 FILE_SCAN 2.1645594E-002
2 PARTITION_ACCESS 2.1645594E-002
3 ROOT 1.1964559E-001
--- 3 row(s) selected.
For a description of all column fields of the EXPLAIN function, see Description of the
EXPLAIN Function Results on page 4-3.
For a detailed description of the EXPLAIN function, see Displaying Selected Columns
of the Execution Plan on page 4-5.
Using the DISPLAY_EXPLAIN Shortcut
You can display all columns of the execution plan by using the DISPLAY_EXPLAIN
command. This command is a shortcut for the EXPLAIN function.
In MXCI, enter DISPLAY_EXPLAIN followed by your query. For example:
DISPLAY_EXPLAIN
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEE
WHERE SALARY > 40000.00;
You can also use DISPLAY_EXPLAIN to display the plan for a prepared query.
For a detailed description of the DISPLAY_EXPLAIN command, see Using
DISPLAY_EXPLAIN to Review the Execution Plan on page 4-6.
Using the Visual Query Planner
1. To start the Visual Query Planner, do one of the following:
•
Select Start > Programs > NonStop SQL-MX >Visual Query Planner.
•
Navigate to the C:\Program Files\Hewlett Packard\NonStop SQL-MX folder
and select Visual Query Planner.
2. Select Explain > Connect to ODBC to connect to your ODBC data source.