Oracle Tuning - Tuning Joining methods

There are 3 Join methods for Join operators of Oracle optimizer as follows :
  • Hash join
  • Nested loops
  • Sort-merge join

    • Oracle Hash Join :
    Hash join is performed only for equijoins and most useful for joining large data sets ( Large Tables, Views ). Oracle optimizer uses the two small tables to build a hash table on the join key in the memory, then it scans the larger table and probing the hash table to find the joined rows.
    The execution step of Hash join is as follows
    ora-tuning1

    You can use USE_HASH hint in the SQL Statement to instruct the optimizer for using Hash join when joining large tables.

    Oracle Nested Loops Join :
    Nested Loops join is most useful for joining small data sets ( SmallTables, Views ), if the join condition is appropriate for accessing the second table.
    ora-tuning2

    You can see the Nested loop join and its types in the execution plan as follows. ora-tuning3

    You can use USE_NL hint in the SQL Statement to instruct the optimizer for using Nested Loop join

    Oracle Sort-merge join :
    Sort-merge join is used to join rows from two independent sources (Tables). All row sources are sorted by the same sort key, then sorted rows are merged as follows.
    ora-tuning4

    If one or more row sources are already sorted, then sort merge join is prefered instead of hash join, if not Hash join is generally better than sort merge join.
    If the join condition of tables is an inequality condition ( such as <, <=, >, or >= ) and sorting is required, then Oracle optimizer can select the Sort merge join instead of Hash join for joining large data sets ( Large Tables, Views ).
    You can use USE_MERGE hint in the SQL Statement to instruct the optimizer for using Sort merge join.



    (Oracle Tuning - Tuning Partitions)