Understanding Join Types in Oracle Execution Plans

1. Introduction

In Oracle Database, a SQL join written in a query (INNER JOIN, LEFT OUTER JOIN, etc.) does not directly map to what you see in the execution plan. Instead, the Cost-Based Optimizer (CBO) transforms logical joins into physical join operations based on statistics, indexes, data volume, and available access paths.

Understanding join types in execution plans is critical for:

• Performance tuning
• Diagnosing slow queries
• Correct interpretation of optimizer decisions

2. Logical Joins vs Physical Joins

Logical Joins (SQL Level)

These are what developers write:

• INNER JOIN
• LEFT / RIGHT / FULL OUTER JOIN
• CROSS JOIN

Physical Joins (Execution Plan Level)

These are how Oracle actually executes joins:

• NESTED LOOPS
• HASH JOIN
• MERGE JOIN
• CARTESIAN JOIN
• Variants like HASH JOIN OUTER, MERGE JOIN CARTESIAN

Execution plans always show physical join methods, not logical join syntax.

3. Nested Loops Join

NESTED LOOPS        

How It Works

1. Oracle reads a row from the outer (driving) table
2. For each row, Oracle probes the inner table
3. This usually uses an index on the inner table

Pseudo logic:

FOR each row in outer_table
  FIND matching rows in inner_table        

When Oracle Chooses It

• Small outer table
• Highly selective predicates
• Index available on join column
• OLTP-style queries

Example Plan

NESTED LOOPS
  TABLE ACCESS BY INDEX ROWID EMP
  INDEX RANGE SCAN EMP_DEPT_IDX        

Performance Notes

• Very fast for small datasets
• Poor for large outer tables (can cause many index lookups)

4. Hash Join

Execution Plan Name

HASH JOIN        

How It Works

1. Oracle builds a hash table in memory from the smaller table (build input)
2. Oracle scans the larger table (probe input)
3. Matching rows are found using hash lookup

Pseudo logic:

BUILD hash_table FROM smaller_table
FOR each row in larger_table
  PROBE hash_table        

When Oracle Chooses It

• Large data sets
• No usable indexes
• Equality joins (=)
• Data warehouse queries

Example Plan

HASH JOIN
  TABLE ACCESS FULL DEPARTMENTS
  TABLE ACCESS FULL EMPLOYEES        

Performance Notes

• Efficient for large joins
• Scales well
• Uses PGA memory (can spill to TEMP if insufficient)

5. Merge Join

Execution Plan Name

MERGE JOIN        

How It Works

1. Both inputs must be sorted on join keys
2. Oracle walks through both datasets simultaneously
3. Matches rows like a zipper

Pseudo logic:

SORT table1 ON join_key
SORT table2 ON join_key
MERGE results        

When Oracle Chooses It

• Inputs already sorted (via index)
• Range joins (<, >, BETWEEN)
• Large datasets with ordered access

MERGE JOIN
  SORT JOIN
    TABLE ACCESS FULL EMP
  SORT JOIN
    TABLE ACCESS FULL DEPT        

Performance Notes

• Good for sorted data
• Sorting can be expensive
• Less common than hash joins

6. Cartesian Join

Execution Plan Name

MERGE JOIN CARTESIAN        

How It Works

• Every row from table A is joined to every row from table B

rows(A) × rows(B)        

When It Occurs

• Missing join condition
• Intentional CROSS JOIN
• Query rewrite or optimizer transformation
• Missing Objects Statistics

Example Plan

MERGE JOIN CARTESIAN
  TABLE ACCESS FULL A
  TABLE ACCESS FULL B        

Performance Notes

• Extremely expensive
• Often a sign of a bug or missing condition

7. Outer Join Variants in Execution Plans

Oracle does not display OUTER JOIN directly. Instead, it shows join methods with the word OUTER.

Common Forms

HASH JOIN OUTER
MERGE JOIN OUTER
NESTED LOOPS OUTER        

Meaning

• Preserves rows from the driving table even when no match exists
• Implements LEFT or RIGHT OUTER JOIN semantics

8. Join Direction and Driving Table

Execution plans are read top-down, inside-out.

Key points:

• First child = outer (driving) input
• Second child = inner (driven) input
• Join order affects performance significantly

Use:

• LEADING hint
• USE_NL, USE_HASH, USE_MERGE hints to influence join method and order (carefully).

9. Adaptive and Hybrid Join Behavior

In modern Oracle versions (12c+):

• Join methods can change at runtime
• Plans may show:

HASH JOIN (ADAPTIVE)        

Oracle may start with one join method and switch if row counts differ from estimates.

10. Summary Table

11. Conclusion

Oracle execution plans reveal how joins are physically executed, not how they are written. Understanding join types such as Nested Loops, Hash Join, and Merge Join allows database professionals to:

• Interpret optimizer decisions
• Tune slow SQL effectively
• Avoid performance pitfalls like unintended Cartesian joins

Mastering execution-plan join analysis is a core skill for any Oracle DBA or SQL performance engineer.