Dynamic hash join provides the ability to do an equality join directly between a small table and a large table on non‑primary index columns without placing the large table into a spool file. important criteria for dynamic hash join to be used, the left table must be small enough to fit in a single hash join partition.
It process as follows:
1) Duplicate the smaller table.
2) Place the smaller table in a hash array
3) Read a row from the right table
4) Compute the row hash code
5) Match each right row with all rows in the hash array having the same row hash.
6) Join the matching rows.
Dynamic hash join is faster than redistributing the small table, putting it into the hash array, reading the large table, building the row hash code, writing the data out to spool, and then reading the table in again to do the hash join.
The dynamic hash join can be decided by optimizer in case of left, right, and full outer joins as well as inner joins, and can also take advantage of equality conditions for dynamic partition elimination.
