Nested Loop Joins

Overview Nested Loop Joins
---------------------------------------
•Oracle always join two of the tables. In join one row set is called inner, and the other is called outer. If the inner table row set is dependent or derived from outer table then nested loop join performs better.

•But if the inner table's access path is independent of the outer table, then the same rows are retrieved for every iteration of the outer loop, degrading performance considerably. In such cases, hash joins joining the two independent row sources perform better.

•Nested loop joins are useful when small subsets of data are being joined and if the join condition is an efficient way of accessing the second table.

How Nested Loop Join Works
---------------------------------------------------
In case of nested loop join the following steps is involved.
1)The optimizer first determine the driving table and designates it as the outer table.

2)The optimizer designate other table (driven/dependent) as inner table.

3)For every row in the outer table, Oracle accesses all the rows in the inner table. The outer loop is for every row in outer table and the inner loop is for every row in the inner table. The outer loop appears before the inner loop in the execution plan, as follows:

NESTED LOOPS
outer_loop
inner_loop

When the Optimizer Uses Nested Loop Joins
-----------------------------------------------------------
•The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.

•The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can be used as a row source for another nested loop join.

•The inner loop is iterated for every row returned from the outer loop, ideally by an index scan. If the access path for the inner loop is not dependent on the outer loop, then you can end up with a Cartesian product; for every iteration of the outer loop, the inner loop produces the same set of rows. Therefore, you should use other join methods when two independent row sources are joined together.

Nested Loop Join Hints
------------------------------------------
If the optimizer is choosing to use some other join method, you can use the USE_NL(table1 table2) hint, where table1 and table2 are the aliases of the tables being joined.

Nested Loop Join Example
------------------------------------------
Here I used the script and data that is listed in post Examples of Outer Joins
SQL> select address, orderdate from customers c, orders o where c.custno=o.custno;


Execution Plan
----------------------------------------------------------
Plan hash value: 3442778016

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 275 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 275 | 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 | 44 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C006142 | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("C"."CUSTNO"="O"."CUSTNO")

Here the outer loop is | 2 | TABLE ACCESS FULL | ORDERS |
And inner loop is | 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS |
|* 4 | INDEX UNIQUE SCAN |