Database Reference
In-Depth Information
Merge Join
In the previous case, input from the
Product
table is larger, and the table is not indexed on the joining column
(
ProductCategorylD
). Using the following simple query, you can see different behavior:
SELECT pm.*
FROM Production.ProductModel pm
JOIN Production.ProductModelProductDescriptionCulture pmpd
ON pm.ProductModelID = pmpd.ProductModelID ;
Figure
7-9
shows the resultant execution plan for this query.
Figure 7-9.
Execution plan with a merge join
For this query, the optimizer used a merge join between the two tables. A merge join requires both join inputs
to be sorted on the merge columns, as defined by the join criterion. If indexes are available on both joining columns,
then the join inputs are sorted by the index. Since each join input is sorted, the merge join gets a row from each input
and compares them for equality. A matching row is produced if they are equal. This process is repeated until all rows
are processed.
In situations where the data is ordered by an index, a merge join can be one of the fastest join operations, but if
the data is not ordered and the optimizer still chooses to perform a merge join, then the data has to be ordered by an
extra operation, a sort. This can make the merge join slower and more costly in terms of memory and I/O resources.
In this case, the query optimizer found that the join inputs were both sorted (or indexed) on their joining
columns. You can see this in the properties of the
Index Scan
operators, as shown in Figure
7-10
.
Figure 7-10.
Properties of Clustered Index Scan showing that the data is Ordered
As a result of the data being ordered by the indexes in use, the merge join was chosen as a faster join strategy than
any other join in this situation.
