Nested Loop Join in Oracle 11g

Nested Loop Join in Oracle 11g

What is  Nested Loop Join in Oracle

-For each row in the first row source access all the rows from the second row source.
-The NESTED LOOPS Join is a join operation that selects a row from the selected beginning row source and uses the values of this row source to drive into or select from the joined row source searching for the matching row.
-Best for OLTP type transactions
-it will be Fastest if rows returned from first table are small

-Nested Loop Join in Oracle 11g works differently.Details about  it are given in this article

how nested loop join works in oracle

-The optimizer first determine the driving table and designates it as the outer loop.This 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 index scan or full table scan. The rows can be produced from any other operation too. For example the output from a Nested Loop Join can be used as a row source.
-The optimizer designate other table as inner Loop.This is iterated for every row returned from the outer loop. This is an access operation on a table and ideally should be an index scan.
-Operation performed by INNER table is repeated for every row returned in OUTER table

 

for  x in (select  from outer table)

loop

    for row in (select from inner table )

loop

  joined output rows is returned where condition is matched

end loop

end loop

Nested Loop Join Example

select /* +use_nl( a b) */ a.emp_no,b.dept_no,b.dept_name from emp a,dept b where a.dept_no=b.dept_no;

SELECT STATEMENT

NESTED LOOP

TABLE ACCESS FULL DEPT

TABLE ACCESS BY INDEX ROWID EMP

INDEX RANGE SCAN EMP_N1

How it is processed

1) Read the first row in DEPT
2) Do the index scan on EMP_N1 for matching dept_no and get the first rowid
3) Look for the refrenced row in EMP and join the information and provide output
4) Repeat operation 2,3 for each rowid returned
5) Repeat the operation 1,2,3,4 for all the rows in DEPT

for x in (select from table dept)
loop
for row in (select from table emp )
loop
joined output rows is returned where condition is matched
end loop
end loop

Here the access method for select from table emp would be through index scan
If the optimizer is choosing to use some other join method, you can use the USE_NL(A B) hint, where A and B are the aliases of the tables being joined.

Cost Calculations
In a NESTED LOOPS join, for every row in the outer row set, the inner row set is accessed to find all the matching rows to join. Therefore, in this type of join, the inner row set is accessed as many times as the number of rows in the outer row set.
Cost : cost to acess table A + no of rows returned from table A X cost to access table B

So this will be cost effective if small rows are returned from outer table and access operation for inner table is through unique scan or small index range scan

New Method for Nested Loop Join in Oracle 11g

SELECT STATEMENT
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS FULL DEPT
INDEX UNIQUE SCAN EMP_IDX
TABLE ACCESS BY INDEX ROWID EMP

Oracle documentations says this about
When an index or a table block is not in the buffer cache and is needed to process the join, a physical I/O is required. In Oracle Database 11g Release 1 (11.1), Oracle Database can batch multiple physical I/O requests and process them using a vector I/O instead of processing them one at a time. As part of the new implementation for nested loop joins, two NESTED LOOPS join row sources might appear in the execution plan where only one would have appeared in prior releases. In such cases, Oracle Database allocates one NESTED LOOPS join row source to join the values from the table on the outer side of the join with the index on the inner side. A second row source is allocated to join the result of the first join, which includes the rowids stored in the index, with the table on the inner side of the join”

 

Nested Loops outer join in Oracle

-Similar to nested loop
-Rows returned even if inner loop does not have any rows meeting the criteria
-Unlike the nested loop which might be driven from either of the tables, this is one way join
a = b(+) will always go to a before b, this may result in more expensive plan (possibly non-NL)
(+) always goes on deficient side