In cases where a very small table is being joined to a large table, the Oracle hash join will often dramatically speed-up the query. Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table.
However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins. For large tables, hash joins requires lots of RAM.
Oracle places limits of the amount of RAM available for hash joins so that no single session will "hog" all of the RAM.
Read more about Oracle hash
Oracle hash joins tips
