MergeJoin reads two inputs that are both ordered by the join attributes. For each row of the left input, the algorithm reads all the matching rows of the right input by accessing the rows in sorted order.
If the inputs are not already ordered by the join attributes (perhaps because of an earlier merge join or because an index was used to satisfy a search condition), then the optimizer adds a sort to produce the correct row order. This sort adds cost to the merge join.
One advantage of MergeJoin, as compared to HashJoin, is that the cost of sorting can be amortized over several joins, if the merge joins are over the same attributes. The optimizer chooses MergeJoin over HashJoin if the sizes of the inputs are likely to be similar, or if it can amortize the cost of the sort over several operations.
Discuss this page in DocCommentXchange.
|Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0|