Optimizer choise of Outer Join in execution plan

Nested Loop Outer Joins
--------------------------------------------
This operation is used when an outer join is used between two tables. The outer join returns the outer table rows, even when there are no corresponding rows in the inner table.

In a regular outer join, the optimizer chooses the order of tables based on the cost. However, in a nested loop outer join, there is not alternative. The order of tables is determined by the join condition. The outer table, with rows that are being preserved, is used to drive to the inner table.

To use nested loop outer join explicitly USE_NL hint can be used.

Here is an example to use nested loop outer joins in oracle.

Based on the data in Example of Outer Join

SQL> select CustName, OrderDate, Address
from Customers c Right outer join Orders o
on c.CustNo = o.CustNo ;


Execution Plan
----------------------------------------------------------
Plan hash value: 1206303405

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 380 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 5 | 380 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | ORDERS | 5 | 55 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 65 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C006142 | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Hash Join Outer Joins
--------------------------------------------
The optimizer uses hash joins for processing an outer join if the data volume is high enough to make the hash join method efficient or if it is not possible to drive from the outer table to inner table.

Based on the data in Examples of All outer joins in Oracle

SQL> select a.n, b.k from table_a a left outer join table_b b on a.k=b.k;

10000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2129125445

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 166K| 603 (3)| 00:00:08 |
|* 1 | HASH JOIN OUTER | | 10000 | 166K| 603 (3)| 00:00:08 |
| 2 | TABLE ACCESS FULL| TABLE_A | 10000 | 90000 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TABLE_B | 1007K| 7873K| 589 (2)| 00:00:08 |
------------------------------------------------------------------------------

Sort Merge Outer Joins
--------------------------------------------
When an outer join cannot drive from the outer table to the inner table, it cannot use a hash join or nested loop joins. Then it uses the sort merge outer join for performing the join operation.

SQL> select CustName, OrderDate, Address
from Customers c left outer join Orders o
on c.CustNo = o.CustNo ;

Based on the data in Example of Outer Join


Execution Plan
----------------------------------------------------------
Plan hash value: 1595815448

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 380 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 5 | 380 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 4 | 260 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | SYS_C006142 | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 5 | 55 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | ORDERS | 5 | 55 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Full Outer Joins
---------------------------------------------
A full outer join acts like a combination of the left and right outer joins. you can specify FULL OUTER JOIN.