This is one of the table join mechanisms in Oracle. There are three types of joining methods including nested loop join in Oracle. Below are those types that merge two or more tables.
- Hash Join
- Nested loop
- Sort Merge
When the Oracle Considers Nested Loops Joins
in general, nested loop join in Oracle works well on small tables. But the decision was taken by the optimizer. Below are the suitable situations in which Oracle decided to merge with nested loop join. Hash joins best when joining datasets that fit with memory and are good with big datasets as well.
- The small size of the table merges
- Joins small subset of datasets in big tables.
- Joins large set but Mode set to FIRST_ROWS
- Joins billions of record tables but there is a filter that expects few for each time.
- Join condition available in an index.
- Join table values filter by primary key (Employee_id =100)
This is not the only measurement to select merge tables with nested loop join in Oracle. There are more but everything is up to the optimizer to decide. The optimizer tries to put the smallest source first as a driving table.
How Nested Loops Join Oracle Execute
This is easy to understand by thinking about loop inside a another loop or two nested loops. Let’s take our common example of DEPARTMENT and EMPLOYEE.
-- FOR emp IN (select * from employee) LOOP FOR dep IN (select * from department is matched) LOOP output values from emp and dep END LOOP END LOOP --
- First identified driver table. In this case Employee table. It is using the outer loop
- Outer table produced driving rows and those are fetched by full table scan or other type of index scan (Ex range scan)
- The number of fetches in the inner loop depends on the outer loop row count. If there are 10 rows in the outer loop, then there are 10 times fetch from the inner loop.
- The execution plan shows the outer loop first and then the inner loop in the plan
- Once matching rows fount when looping those records are returned as merge.

Nested Loops Join Example
let’s add records for the department and employee tables. Then merge two tables using the department id.
-- SELECT /*+ ORDERED USE_NL(d) */ e.employee_name, d.department_name FROM employee_table e, departments d WHERE e.department_id=d.department_id; --
Now retrieve the execution plan for the above query. Hope optimizer chooses nested loop join in oracle to merge tables. Beloved is the sample code for inserting data into the tables. Not going to add too much data to the table. This is because nested loop joins work on small tables.

Below are the basic steps for the execution plan.
- Database scan using index unique scan in Oracle and returns rowids for departments. (Id =4)
- Then do the table access full because there are no indexes for foreign keys on the department ID in the employee table. (id =3)
- Once both records the optimizer decides to drive the table as a department table. (id=5).
- TABLE ACCESS BY INDEX ROWID is used to retrieve value for the department table.
- Once decided on the driving table as a department now we have the outer table as the department. When iterating the outer loop in step 1 (id=1) record fetch using TABLE ACCESS BY INDEX ROWID and match with inner table employee.
- This happens until the end of the outer loop. Then all the matching records return and Join Completed.
- Finally, execute on a select statement and fetch values.
Nested Loops Hints
We can add /*+ ORDERED USE_NL(d) */
hint if required. This forces the optimizer to merge on nested loop join. This is actually not required because now Oracle decides the best plan for a query.
Sample Code for Example
-- CREATE TABLE employee_table( employee_id VARCHAR2(50) , employee_name VARCHAR2(150) , department_id VARCHAR2(50) , PRIMARY KEY(employee_id) ); CREATE TABLE departments ( department_id VARCHAR2(50) , department_name VARCHAR2(50) , PRIMARY KEY(department_id) ); declare -- insertemployee 1000000 records into database i integer:=1000; begin LOOP i:= i+1; INSERT INTO employee_table (employee_id, employee_name,department_id) VALUES ('emp-'||i, 'emp-name'||i,'D-'||round(dbms_random.value(1,99),0)); EXIT WHEN i > 9999; END LOOP; end; declare -- insert 1000000 records into database i integer:=0; begin LOOP i:= i+1; INSERT INTO departments (department_id,department_name) VALUES ('D-'||i, 'de-name'||i); EXIT WHEN i > 99; END LOOP; end; --
Summary
This is the fastest way of merging two or more tables together. nested loop join in Oracle or other types of merge type decided by the optimizer. Explain plan is showing the plan of merging method.