Algorithm for merge join with inequality condition

Question

I read that Oracle supports merge join with inequality join predicates. Is there online reference to algorithm used in implementation of such join ? If anyone knows how to do that, Can you put it in answer?

Answer 1

This is what you're looking for.

7.4 Sort Merge Joins

Sort merge joins can join rows from two independent sources. In general, hash joins perform better than sort merge joins. However, sort merge joins can perform better than hash joins if both of the following conditions exist:

The row sources are sorted. A sort operation is not required. However, if a sort merge join involves choosing a slower access method (an index scan as opposed to a full table scan), then the benefit of using a sort merge might be lost.

Sort merge joins are useful when the join condition between two tables is an inequality condition such as <, <=, >, or >=. Sort merge joins perform better than nested loops joins for large data sets. Hash joins require an equality condition.

In a merge join, there is no concept of a driving table. The join consists of two steps:

Sort join operation

Both the inputs are sorted on the join key.

Merge join operation

The sorted lists are merged.

If the input is sorted by the join column, then a sort join operation is not performed for that row source. However, a sort merge join always creates a positionable sort buffer for the right side of the join so that it can seek back to the last match in the case where duplicate join key values come out of the left side of the join.