7.4 Sort merge when merging
Combine sorting combines rows from two independent sources. In general, hash joins work better than sorting joins. However, sort merge join may work better than hash joins if both of the following conditions exist:
String sources are sorted. Sort operation is not required. However, if merging by sort merge involves choosing a slow access method (index scan, as opposed to a full table scan), then the advantage of using merge sort can be lost.
Sort merge joins are useful when the join condition between two tables is an inequality condition such as <, lt; =,>, or> =. Sort joins are better than nested loops join for large datasets. A hash joins the required equality condition.
In a merged pool, there is no concept of a driving table. Joining consists of two steps:
Connection Sort Operation
Both inputs are sorted by connection key.
Combine join operation
Sorted lists are combined.
If the input is sorted by a join column, then the sort sort operation is not performed for this row source. However, the sort join always creates a positional sort buffer for the right side to join, so that it can return to the last match in the event that duplicate join key values exit the left side of the join.