Hint                                                                               When Ignored

Cluster                                                               When Used with a nonclustered table

Hash                                                                   When used with a Nonclustered table

Hash_aj                                                              When no Sub_query Exists

Index                                                                  When the specified Index does not exist

index_combine                                                 When no bitmapped_indexes exist

Merge_aj                                                           When no Sub_query Exists

Push_subq                                                        When NO sub_query Exists

star                                                                     When No Proper index exists on the fact table

use_nl                                                                 When there is no index on any of the tables in Join

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;