Databases Reference
In-Depth Information
In this operation, the algorithm selected by the database engine is the Hash Join.
To execute the join, the database first creates a hash table based on the join fields of the
inner table. It then executes the query for nested loops, locating the rows which satisfy the
join condition using the hash function.
In our example, we can see the full table scan of the
MY_COUNTRIES
table, used to build
the hash table, and the full table scan of
MY_CUSTOMERS
—scrolling the last table. The
hash sort is executed using the hash function to locate the corresponding values of the
MY_CUSTOMERS
table.
The hash join is quickly executed, but it can be used only when we have an equi-join
condition, because we cannot perform a range operation on the hash table, to satisfy
for example, a "greater than" condition.
In step 6, we can see a slightly modified version of the previous example. Here the equi-join
condition is replaced by a range match criteria (this makes no sense in the real world, but is
useful to explain the matter).
We can see the execution plan for this query in the following screenshot:
