Oracle Database USE_HASH


Instructs the engine to use hash method to join tables in the argument.

Usage : use_hash(TableA [TableB] ... [TableN])

As explained in many places, "in a HASH join, Oracle accesses one table (usually the smaller of the joined results) and builds a hash table on the join key in memory. It then scans the other table in the join (usually the larger one) and probes the hash table for matches to it."

It is preferred against Nested Loops method when the tables are big, no indexes are at hand, etc.

Note: The hint does not force the order of the join, just asks for HASH JOIN method.

Example of usage:

SELECT /*+use_hash(e d)*/ *
FROM Employees E
JOIN Departments D on E.DepartmentID = D.ID