Results from the worker processes that process a query are merged using one of the following types of merge:
Parallel worktable merge
Parallel network buffer merge
Parallel result buffer merge
Parallel work table merge.
Grouped aggregate results from the worktables created by each worker process are merged into one result set.
In the following example, titles has two partitions. The showplan information specific to parallel query processing appears in bold.
select type, sum(total_sales)
from titles
group by type
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT (into Worktable1).
GROUP BY
Evaluate Grouped SUM OR AVERAGE AGGREGATE.
Executed in parallel by coordinating process and 2 worker processes.
FROM TABLE
titles
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 2-way partition scan.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
Parallel work table merge.
STEP 2
The type of query is SELECT.
Executed by coordinating process.
FROM TABLE
Worktable1.
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
See “Merge join messages” on page 824 for an example that uses parallel processing to perform sort-merge joins.
Parallel network buffer merge.
Unsorted, non aggregate results returned by the worker processes are merged into a network buffer that is sent to the client. In the following example, titles has two partitions.
select title_id from titles
QUERY PLAN FOR STATEMENT 1 (at line 1).
Executed in parallel by coordinating process and 2 worker processes.
STEP 1
The type of query is SELECT.
Executed in parallel by coordinating process and 2 worker processes.
FROM TABLE
titles
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 2-way partition scan.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
Parallel network buffer merge.
Parallel result buffer merge.
Ungrouped aggregate results or unsorted, non aggregate variable assignment results from worker processes are merged.
Each worker process stores the aggregate in a result buffer. The result buffer merge produces a single value, ranging from zero-length (when the value is NULL) to the maximum length of a character string.
In the following example, titles has two partitions:
select sum(total_sales) from titles
QUERY PLAN FOR STATEMENT 1 (at line 1).
Executed in parallel by coordinating process and 2 worker processes.
STEP 1
The type of query is SELECT.
Evaluate Ungrouped SUM OR AVERAGE AGGREGATE.
Executed in parallel by coordinating process and 2 worker processes.
FROM TABLE
titles
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Executed in parallel with a 2-way partition scan.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
Parallel result buffer merge.
STEP 2
The type of query is SELECT.
Executed by coordinating process.
