Concatenation, Merge Join Concatenation and Hash Match Union
Concatenation, Merge Join Concatenation and Hash Match Union
By : Kasim Wirama, MCDBA
Other operators that you might encounter in execution plan are concatenation, merge join concatenation and hash match union. Usually these operators are generated when your query contains UNION or UNION ALL. Let’s look each of them below:
Concatenation
The operator happens when your query contains UNION ALL. For example :
SELECT lastname , city, country FROM employees
UNION ALL
SELECT contactname, city, country FROM customers;
The corresponding execution plan is :
|--Concatenation
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Employees].[PK_Employees]))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]))
Notice that two clustered index scans are join with concatenation operator with any attributes on it. It just joins two input sets become one without eliminating duplicate rows from both inputs.
Merge Join Concatenation
Merge join operator happens when select list columns get indexed and sorted. The example query is :
SELECT lastname , city, country FROM employees
UNION ALL
SELECT contactname , city, country FROM customers
ORDER BY lastname;
Before executing above query, create index lastname, city, country on employes table and contactname, city, country on customers table.
Execution plan for above query is :
|--Merge Join(Concatenation)
|--Index Scan(OBJECT:([Northwind].[dbo].[Employees].[ix_1]), ORDERED FORWARD)
|--Index Scan(OBJECT:([Northwind].[dbo].[Customers].[ix_1]), ORDERED FORWARD)
Hash Match Union
Other possible operator for UNION query is hash match. Hash match happens when you join a huge table with many duplicate with a table with unique column.
For example :
CREATE TABLE hugetable
(cola INT PRIMARY KEY ,
colb INT,
colc CHAR(1000));
CREATE TABLE uniquetable
(cola INT PRIMARY KEY,
colb INT UNIQUE,
colc CHAR(1000));
Populate hugetable with 500000 rows (large number of rows).
If you issue query below, you will get hash match operator :
SELECT colb, colc FROM hugetable
UNION
SELECT colb, colc FROM uniquetable
The execution plan is :
|--Hash Match(Union)
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[hugetable].[PK__hugetable__4BAC3F29]))
|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[uniquetable].[PK__uniquetable__5070F446]))
With characteristic of your table design, query statement, and row distribution, you will predict what optimizer will likely choose between these operators to produce feasible execution plan.