Hints

Hints :

index (tab_name index_name)
index(my_tab my_tab(col_1, col_2))
NO_INDEX(emp emp_ix)
LEADING (dept)
FULL
HASH : USE_HASH (s i)
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
NO_INDEX
AND_EQUAL

MERGE
STAR_TRANSFORMATION
DRIVING_SITE
HASH_AJ
HASH_SJ
LEADING
MERGE_AJ
MERGE_SJ
NL_AJ
NL_SJ
USE_HASH : USE_HASH (s i)
USE_MERGE : USE_MERGE (s i)
USE_NL : USE_NL(l h)
NO_USE_MERGE : NO_USE_MERGE(e d
UNNEST : Used in subquery
NO_UNNEST : Used in subquery

STAR
CACHE
INLINE
PUSH_PRED
PUSH_SUBQ
NO_PUSH_PRED
NO_PUSH_SUBQ
ORDERED : join tables in the order in which they appear in the FROM clause
ORDERED_PREDICATES

• Hash joins are usually used joining large data sets. 
• Hash joins are very much affected by available memory and number of blocks to join is also dependant on available memory
• If one uses workarea_size_policy = auto then hash_area_size doesn’t matter, instead pga_aggregate_target is used and a portion of it is allocated to hash join.