SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-25
EXPLAIN Plan for ORDER BY
EXPLAIN Plan for ORDER BY
This query requires a sort operation to present columns in a specific order:
EXPLAIN
SELECT PARTNUM,PRICE,QTY_AVAILABLE
FROM PARTS
ORDER BY 2 DESC, 3 ASC, 1 ;
The total cost of the query is 3.
The plan consists of one step involving two operations: a scan of the PARTS table and
a sort operation.
Example 6-14. EXPLAIN Plan for SELECT With ORDER BY
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
---------------------------------------------------------------------------
Operation 1.0 : Scan
Table : \SQL1.$DATA8.SALES.PARTS
with correlation name PARTS
Access type : Record locks, stable access
Lock mode : Chosen by the system
Column processing : Requires retrieval of 3 out of 4 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: 28 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 : PARTS.PRICE desc, PARTS.QTY_AVAILABLE asc,
PARTS.PARTNUM asc
Sort cost : 1
Total cost : 3