SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-28
SELECT With GROUP BY Using a Parallel Plan
SELECT With GROUP BY Using a Parallel Plan
This query creates an index on DEPTNUM and requests parallel execution:
CONTROL EXECUTOR PARALLEL EXECUTION ON ;
CREATE INDEX XDEPT ON EMPLOYEE (DEPTNUM,JOBCODE)
PARTITION ( $DATA2.PERSNL.XDEPT FIRST KEY 4,
$DATA5.PERSNL.XDEPT FIRST KEY 7);
CONTROL TABLE EMPLOYEE ACCESS PATH INDEX XDEPT ;
EXPLAIN
SELECT DEPTNUM, JOBCODE, COUNT(*) FROM EMPLOYEE
GROUP BY 1,2;
Example 6-16. EXPLAIN Plan for SELECT With GROUP BY Using a Parallel
Plan (page 1 of 2)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Query plan 1 : Will utilize parallel execution
SQL request : Select
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
---------------------------------------------------------------------------
Plan step 1
Each operation is performed in parallel for this step
Each ESP will read one of the following partitions:
\SQL1.$DATA2 \SQL1.$DATA5 \SQL1.$DATA8
The ESP's will be started in the cpu's numbered
0 2 1
Group By will be performed by 3 ESP's in parallel
---------------------------------------------------------------------------
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
Access path 1 : Alternate \SQL1.$DATA8.PERSNL.XDEPT, index only,
partitioned, path forced
SBB for reads : Virtual
Begin key pred. : None
End key pred. : None
Index selectivity : Expect to examine 100% of rows from index
Index pred. : None
Base table pred. : None