In a Hash Join, each table is first read into main memory seperately by a TABLE ACCESS FULL operation, and then a hashing function is used to compare the second table to the first table. The rows that result in matches are then returned to the user.  Hash Joins use memory (because two tables are kept in the memory), So application that make extensive use of HASH JOINS may need to increase the amount of memory available in the System Global Area (SGA). The tables should be small when hash join is required for a oltp or an online query.

An example of oracle Use_hash hint


SELECT /*+ USE_HASH */ *
FROM EMP,JOB
WHERE EMP.JOBCODE=JOB.JOBC;