SQL/MX 3.x Query Guide (H06.22+, J06.11+)

Reviewing Query Execution Plans
HP NonStop SQL/MX Query Guide—640323-001
4-2
Using the EXPLAIN Statement 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 EXPLAIN Statement Shortcut
You can display all the columns of the execution plan using the EXPLAIN statement.
This command is a shortcut for the EXPLAIN function.
In MXCI, enter the EXPLAIN statement followed by your query. For example:
EXPLAIN
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEE
WHERE SALARY > 40000.00;
You can also use the EXPLAIN statement to display the plan for a prepared query.
For more information about the EXPLAIN statement, see Using the EXPLAIN
Statement 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.