Databases Reference
In-Depth Information
In the following screenshot, you can see statistics for the same query:
In the previous example, we have seen a Sort-Merge Join in action. This algorithm works
as follows:
1. The first table is sorted by join fields.
2. The second table is sorted by join fields.
3.
A merge operation between the two sorted datasets is executed.
The Sort-Merge Join is more efficient than the NESTED LOOPS and doesn't need indexes
to quickly access the inner table. It's slower than the Hash Join, but can be used for
nonequi-join queries, as the one in our example.
There's more...
There are some considerations to be kept in mind when choosing the join method that
best fits your needs.
For example, Sort-Merge Joins joins are based upon two sort operations, which uses
memory and CPU, while Hash joins use memory to generate the hash table.
Generally speaking, nested loops are used only when there is an index on the inner
table—the table which is scanned for every row in the outer table—and the rows of the
inner table intersected by the join are a small subset (so scanning the entire table to
merge-sort or to build the hash table isn't efficient); otherwise the hash join method
is preferred.
There are also some tricks to avoid the join operation—denormalization, clusters, and
materialized views.
