SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-18
EXPLAIN Plan for Primary Access
EXPLAIN Plan for Primary Access
Example 6-8 and Example 6-9 show different EXPLAIN plans for the same query: one
plan uses primary access; the other uses index-only access.
The 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-8 shows the EXPLAIN plan for the query before creating an index on the
LAST_NAME, FIRST_NAME columns.
SQL must sort the rows to satisfy the ORDER BY clause. A detailed analysis of the
plan follows.
Example 6-8. EXPLAIN Plan Choosing Primary Access
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.PERSNL.EMPLOYEE
with correlation name EMPLOYEE
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 3 out of 6 columns
Access path 1 : Primary
SBB for reads : Virtual
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from table
Index pred. : None
Base table pred. : None
Executor pred. : None
Table selectivity : Expect to select 100% of rows from table
Expected row count: 57 rows after the scan
Operation cost : 2
Operation 1.1 : Sort
Requested : Explicitly in the query
Sort rows in the : Result of a Select
Purpose : To order rows for an Order By
Sort technique : FASTSORT
Sort type : Plan to use User Process Sort
UPS workspace : 24 Kbytes
Sort key columns : EMPLOYEE.LAST_NAME asc, EMPLOYEE.FIRST_NAME asc
Sort cost : 1
Total cost : 3