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 oracle 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 referenced 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 oracle 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
Also Reads
Hash Join
Join method in Oracle
https://en.wikipedia.org/wiki/Nested_loop_join