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

Parallelism
HP NonStop SQL/MX Query Guide—523728-003
8-14
How to Determine if You Have a Parallel Plan
The query plan shows a parallel matching partitions merge join. Two ESPs are started
to perform the merge join in parallel (as indicated by the bottom_degree_parallelism
token for the ESP_EXCHANGE). Also, the bottom_partitioning_function token
indicates that the matching partitions join is range partitioned two ways on the column
specified. The tokens relating to parallelism are discussed under Understanding the
Parallelism Tokens in the EXPLAIN Output on page 8-16.
A portion of the EXPLAIN output follows:
Seq_Num: 6
Operator: ESP_EXCHANGE
Left_Child_Seq_Num: 5
Right_Child_Seq_Num: ?
Cardinality: 1.7563164E+004
Operator Cost: 1.0772745E+000
Total Cost: 6.0278694E+001
Detail Cost: CPU_TIME: 43.9642 IO_TIME: 59.8779
MSG_TIME: 1.30885 IDLETIME: 0.400804 PROBES: 1
bottom_partition_input_values: \:_sys_HostVarLo0,
\:_sys_HostVarHi0, \:_sys_hostVarExclRange
buffer_size: 3951
record_length: 288
top_degree_parallelism: 1
bottom_degree_parallelism: 2
top_num_data_streams: 1
bottom_num_data_streams: 2
bottom_partitioning_function: range partitioned 2 ways on
(([150]equiv(O.O_ORDERKEY)))
Figure 8-2 on page 8-15 shows the same query executed by using DAM parallelism to
access the partitioned tables. This query plan shows a serial hybrid hash join with no
ESPs. However, DAM parallelism is demonstrated by the SPLIT_TOP operator in
accessing the underlying partitioned tables. The bottom_partitioning_function token
Figure 8-1. Matching Partitions Join Using ESP Parallelism
root [7]
esp exchange [6]
merge_join [5]
partition access [2] partition access [4]
file_scan ORDERS [1] file_scan LINEITEM [3]
VST085.vsd