SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-26
EXPLAIN Plans for GROUP BY
The sort operation is requested explicitly in the query as a result of the ORDER BY
clause. The purpose of the sort is to order columns in a specific order. The sort
technique is FastSort.
The columns are sorted in the following order:
•
The PRICE column in descending order
•
The QTY_AVAILABLE column in ascending order
•
The PARTNUM column in ascending order
EXPLAIN Plans for GROUP BY
These examples show EXPLAIN plans that use GROUP BY to group data.
SELECT With GROUP BY Using a Serial Plan
This query requires a hash operation to group rows for an aggregate function:
CONTROL EXECUTOR PARALLEL EXECUTION OFF;
EXPLAIN
SELECT DEPTNUM, JOBCODE, COUNT(*) FROM EMPLOYEE
GROUP BY 1,2;
The plan, which consists of one step, is performed serially. The total cost of the query
is 3.
Example 6-15. EXPLAIN Plan for SELECT With GROUP BY Using a Serial
Plan (page 1 of 2)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
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 2 out of 6 columns