Why sort-merge gives the best implementation of the natural join

Abstract

To join two relations efficiently, they must not only be clustered but mutually clustered . Sorting is the only known way to achieve mutual clustering. Once the relations are sorted, merging is the obvious way to implement the join. If the relations are known to be sorted appropriately, the most costly part of the process can be omitted. To know that a relation is sorted already, it is best to remember that we sorted it. Otherwise detecting that the relation is sorted requires inspection of each tuple, a cost comparable in many cases to the cost of sorting. Detecting any other form of mutual clustering is bound to be a combinatorial process of much greater cost than sorting. Cheaper forms of detection may be possible using sampling, but these are heuristics at best and there is no reliable foundation for them.It is assumed that the sizes of the relations are large compared with the RAM memory available or, equivalently, compared with the number of processors available. Our conclusion that sort-merge is best is thus applicable to single-processor computers or to multiple-processor database machines. Discussing the cost of joining relations without making this assumption is outside the subject of databases.