SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Parallelism
HP NonStop SQL/MX Query Guide—523728-003
8-22
Degree of Parallelism
To further understand the degree of parallelism, this is the same query with no ESPs.
Figure 8-7 shows the use of DAM parallelism. The DISPLAY_EXPLAIN OPTIONS 'f'
SQLQUERY shows the output.
PREPARE SQLQUERY FROM
SELECT l_orderkey, CAST(SUM(l_extendedprice*(1-l_discount))
AS NUMERIC(18,2)), o_orderdate, o_shippriority
FROM customer,orders,lineitem
WHERE c_mktsegment = 'BUILDING'
AND c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND o_orderdate < DATE '1995-03-15'
AND l_shipdate > DATE '1995-03-15'
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY 2 DESC,3 ASC;
Figure 8-8 on page 8-23 shows the query tree for the plan with the plan fragments
identified.
Figure 8-7. DISPLAY_EXPLAIN OPTIONS 'f' SQLQUERY Output for Sample
Query Using DAM Parallelism
LC RC OP OPERATOR DESCRIPTION CARDINALITY
-- -- -- ------------------------- ----------------- -----------
13 . 14 root 1.23E+4
12 . 13 sort 1.23E+4
11 . 12 hash_groupby 1.23E+4
3 10 11 hybrid_hash_join 3.26E+4
6 9 10 hybrid_hash_join 1.51E+4
8 . 9 split_top 1:2(logph) 3.11E+3
7 . 8 partition_access 3.11E+3
. . 7 file_scan CUSTOMER (s) 3.11E+3
5 . 6 split_top 1:2(logph) 7.27E+4
4 . 5 partition_access 7.27E+4
. . 4 file_scan ORDERS (s) 7.27E+4
2 . 3 split_top 1:2(logph) 3.24E+5
1 . 2 partition_access 3.24E+5
. . 1 file_scan LINEITEM (s) 3.24E+5
Note. You might wonder why the DAM plan fragment is represented by the
PARTITION_ACCESS operator instead of the SPLIT_TOP operator, because the SPLIT_TOP
operator represents DAM parallelism. The SPLIT_TOP operator is responsible for dividing the
work between the ESP or master executor and the Data Access Manager. The SPLIT_TOP
operator assigns the work to the Data Access Manager through the PARTITION_ACCESS
nodes, one for each partition.