SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-27
SELECT With GROUP BY Using a Serial Plan
The plan consists of one step involving two operations: a scan of the EMPLOYEE table
and a hash operation. The plan contains this information:
•
The aggregate function COUNT, which is computed for each group, is evaluated at
the executor level:
Executor aggr. : Computed for each group
GROUP BY Using a Serial Plan on page 3-48 lists the conditions that must be met
if you want the disk process to do the aggregation. If all the conditions had been
satisfied, DP2 aggregation would have taken place in this step. The entry in the
EXPLAIN plan would look like this:
DP2 aggregate : Computed for each group
COUNT ( * )
•
The hash operation is requested explicitly in the query as a result of the GROUP
BY clause; its purpose is to group rows for the aggregate function.
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
Executor aggr. : Computed for each group
COUNT ( * )
Table selectivity : Expect to select 100% of rows from table
Expected row count: 57 rows after the scan
Operation cost : 2
Operation 1.1 : Hash
Requested : By the optimizer
Hash rows in the : Result of a Select
Purpose : To form groups of rows for a Group By
Hash key columns : EMPLOYEE.DEPTNUM , EMPLOYEE.JOBCODE
Expected row count: 57 rows after the group by
Hash cost : 1
Total cost : 4
Example 6-15. EXPLAIN Plan for SELECT With GROUP BY Using a Serial
Plan (page 2 of 2)