use_hash hint

The use_hash hint is the most useful for joining a medium size table with a large table. The throughput is high for use_hash hint when compared to Use_NL hint. use_hash hint is mostly used in datawarehousing environments where the overall query execution should be faster returning all rows rather than first row response time.

The use_has hint requests a hash join against the specified tables. Essentially a hash join is a technique wherey Oracle loads the rows from the driving table (the smallest table,after the where clause) into user global area( RAM) defined by the hash_area_size in the init.ora file.

Oracle then uses a hashing technique to locate the rows in the Larger second table.

The following query is an example of using Use_hash hint

Select /*+ use_hash( e,b) */ e.ename, b.bon, hiredate, from emp e, bonus b where e.ename=b.ename;

Source:http://www.expertsharing.com/2008/01/25/use_hash-hint-in-oracle/