The hash union operator uses Adaptive Server hashing algorithms to simultaneously perform a union all operation on several data streams and hash-based duplicate elimination.
The hash union operator is a nary operator that displays this message:
HASH UNION OPERATOR has <N> children.
<N> is
the number of input streams into the operator.
It also displays the name of the worktable it uses, in this format:
HASH UNION OPERATOR Using Worktable <X> for internal storage.
This worktable is used by the hash union operator to temporarily store data for the current iteration that cannot be processed in the memory currently available.
This example demonstrates the use of hash union:
select * from sysindexes union select * from sysindexes QUERY PLAN FOR STATEMENT 1 (at line 8). Executed in parallel by coordinating process and 2 worker processes. 6 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SORT Operator | Using Worktable2 for internal storage. | | |EXCHANGE Operator | | Executed in parallel by 2 Producer and 1 Consumer processes. | | | | |EXCHANGE:EMIT Operator | | | | | | |HASH UNION Operator has 2 children. | | | | Using Worktable1 for internal storage. | | | | |SCAN Operator | | | | | FROM TABLE | | | | | sysindexes | | | | | Table Scan. | | | | | Forward Scan. | | | | | Positioning at start of table. | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | With LRU Buffer Replacement Strategy for data | | | | | pages. | | | | | | |SCAN Operator | | | | | FROM TABLE | | | | | sysindexes | | | | | Table Scan. | | | | | Forward Scan. | | | | | Positioning at start of table. | | | | | Using I/O size 2 Kbytes for data pages. | | | | | With LRU Buffer Replacement Strategy for data | | | | | pages.
