Plan Trees

Hash Join

Hash join uses build and probe mechanism for the same equi-join result.

highlighted = computed this step

Hash join uses build and probe

A hash join groups one input by the join key, then probes that structure with rows from the other input. This changes mechanism, not the relational result. Note: the result table is recomputed from the hash join.

hash join\text{hash join}

Hash join result

The hash join row count is 3. Note: hash build and probe order cannot change the canonical result order.

hash rows=3\text{hash rows}=3

cardinalities are exact counts on these tiny tables; choosing a plan by cost needs statistics and is deferred - no speed/perf claims; nested-loop vs hash is mechanism, not speed.

Hash join hash join rows 3 scan Students rows 3 scan Enroll rows 3 result sid name course s1 Ann DB s1 Ann OS s2 Bo DB

Same row count

The comparison nested-loop row count is also 3. Note: the two mechanisms recompute the same row set.

nested loop rows=3\text{nested loop rows}=3

cardinalities are exact counts on these tiny tables; choosing a plan by cost needs statistics and is deferred - no speed/perf claims; nested-loop vs hash is mechanism, not speed.

Nested-loop comparison nested loop join rows 3 scan Students rows 3 scan Enroll rows 3 result sid name course s1 Ann DB s1 Ann OS s2 Bo DB

Summary

Hash join is another mechanism for the same equi-join result. Note: choosing by cost needs statistics and is deferred.

same result\text{same result}