SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-10
Generating an EXPLAIN Plan
>> PREPARE Q2 FROM
+> SELECT LAST_NAME, FIRST_NAME, SALARY
+> FROM EMPLOYEE
+> ORDER BY SALARY ;
--- SQL command prepared.
>> EXPLAIN PLAN FOR Q2 ;
You can also specify EXPLAIN as part of the statement syntax:
>> EXPLAIN
+> SELECT LAST_NAME, FIRST_NAME, SALARY
+> FROM EMPLOYEE
+> ORDER BY SALARY ;
A plan provides different types of information for different types of operations. For
instance, Example 6-5 lists the EXPLAIN plan for the preceding query, and shows
information about one scan operation and one sort operation.
Example 6-5. EXPLAIN Plan for Simple Query With ORDER BY
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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.SALARY asc
Sort cost : 1
Total cost : 3