The sort-merge join combines two sorted lists like a zipper. Both sides of the join must be sorted by the join predicates first.Tuning a sort-merge join is like tuning a hash join. That means that the sort-merge join needs indexes on the independent criteria to retrieve all candidate records. Indexing to search by the join predicate is useless. So far, everything like hash join. But there is one property that makes the sort-merge join unique: it is absolutely symmetrical. The join order does not make a difference—not even for performance. This property is very useful for outer joins because outer joins specify the join order for other algorithms—but not for the sort-merge join. The sort-merge join can even do a left and right outer join at the same time (full outer join), like shown in the following animation. Figure 4.1. Sort-Merge Join Executing a FULL OUTER JOIN
Although the sort-merge join performs very well on presorted inputs, it is hardly used because sorting both sides is very expensive. The hash join, on the other hand, needs to preprocess one side only.The strength of the sort-merge join emerges if the input is already sorted as needed. That avoids the sort overhead for the respective input entirely. As an example, think of joining three tables—all on the same join predicates. Remember that each join operation joins two sets only. A three table join needs two steps; first joining two tables, then joining the intermediate result with the third table. The second step, however, doesn't need to sort the intermediate result, because the sort-merge join produces the rows ordered by the join predicates anyway. The optimizer knows that and avoids the unnecessary sort operation. Another way to avoid sorting is to use an index that holds the rows in the required order. That concept is explored in Chapter 6, Sorting and Grouping (not yet published).
Read more: Use the Index, Luke !
QR:
Although the sort-merge join performs very well on presorted inputs, it is hardly used because sorting both sides is very expensive. The hash join, on the other hand, needs to preprocess one side only.The strength of the sort-merge join emerges if the input is already sorted as needed. That avoids the sort overhead for the respective input entirely. As an example, think of joining three tables—all on the same join predicates. Remember that each join operation joins two sets only. A three table join needs two steps; first joining two tables, then joining the intermediate result with the third table. The second step, however, doesn't need to sort the intermediate result, because the sort-merge join produces the rows ordered by the join predicates anyway. The optimizer knows that and avoids the unnecessary sort operation. Another way to avoid sorting is to use an index that holds the rows in the required order. That concept is explored in Chapter 6, Sorting and Grouping (not yet published).
Read more: Use the Index, Luke !
QR:
Posts
0 comments:
Post a Comment