SQL/MP Query Guide

Analyzing Query Performance
HP NonStop SQL/MP Query Guide—524488-003
6-29
EXPLAIN Plans for Subqueries
The plan has one step with two operations:
•
In Operation 1.0, SQL scans the EMPLOYEE table. The executor computes
aggregate values for the local partitions:
Executor aggr. : Computed for each group
COUNT ( * )
GROUP BY Using a Parallel Plan on page 3-49 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 ( * )
•
In Operation 1.1, the master executor uses a hash table to compute the global
aggregate results.
EXPLAIN Plans for Subqueries
These examples show EXPLAIN plans for two types of subqueries: noncorrelated and
correlated.
The queries retrieve data from the ordone and ordtwo tables. Both tables have system-
defined primary keys (SYSKEY).
Noncorrelated Subquery
This query and EXPLAIN plan are for a noncorrelated subquery.
EXPLAIN
SELECT *
FROM ordone
WHERE A = ( SELECT B FROM ordtwo ) ;
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 : 151
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 : 153
Example 6-16. EXPLAIN Plan for SELECT With GROUP BY Using a Parallel
Plan (page 2 of 2)