SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-19
EXPLAIN Plan for Index-Only Access
Plan step 1 involves two operations: a scan of the EMPLOYEE table and a sort
operation.
Operation 1.0 is a scan of the EMPLOYEE table. The query requires that 3 out of 6
columns be retrieved from the table. The EXPLAIN plan for this operation includes the
following:
•
The lock granularity is row (record). The access type is stable (the default).
•
The lock mode is chosen by the system.
•
The access path is the primary key.
•
Virtual sequential block buffering (VSBB) is used to read the table.
•
Index selectivity is 100 percent. This indicates that the optimizer expects that the
entire table will be read.
•
Table selectivity is 100 percent. All rows are selected from the table.
•
The cost of operation 1.0 is 2.
Operation 1.1 is a sort operation requested explicitly in the query as a result of the
ORDER BY clause. The purpose of the sort is to sort rows in a specific order
(LAST_NAME, FIRST_NAME). The EXPLAIN plan for this operation includes the
following:
•
The sort technique is FASTSORT.
•
The columns are sorted in ascending order.
•
The sort cost is 1.
The total cost of the query is 3.
EXPLAIN Plan for Index-Only Access
This query selects all rows from the EMPLOYEE table (primary key EMPNUM) and
orders the rows by LAST_NAME, FIRST_NAME:
EXPLAIN PLAN FOR
SELECT EMPNUM, FIRST_NAME, LAST_NAME
FROM EMPLOYEE
ORDER BY LAST_NAME, FIRST_NAME ;
Example 6-9
on page 6-20 shows the EXPLAIN plan for the query after creating an
index that will satisfy the ORDER BY clause:
CREATE INDEX XEMPNAME
ON EMPLOYEE
(LAST_NAME, FIRST_NAME) ;