Understanding ORACLE HINTS For JOIN OPERATIONS In Oracle Database

By Shoumadip Das
PL/SQL
February 5, 2026
0 Comment

Oracle SQL hints are used to manually control the execution plan of a query, overriding the optimizer’s default decisions regarding join methods and order.

NESTED LOOP JOIN (SGA)

It pick one record from the smaller table and searches for the match in another table and returns the data when match is found.Then it picks the next row from smaller table and do the same comparison with other table.

NL join is chosen when one of the tables is small in size.

USE_NL(table1,table2) :-

This hint tells the optimizer to use NESTED LOOP JOIN.

NO_USE_NL(table1,table2) :-

This hint tells the optimizer NOT to use NESTED LOOP JOIN.

SQL> SELECT /*+ USE_NL(emp,dept) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   308 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    14 |   308 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |    36 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 -  SEL$1 / DEPT@SEL$1
         U -  USE_NL(emp,dept)

SQL> SELECT /*+ NO_USE_NL(emp,dept) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   308 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   308 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

USE_NL_WITH_INDEX :-

This hint tells the optimizer to use NESTED LOOP JOIN with the specified table as inner table and use the specified index.

SQL> SELECT /*+ USE_NL_WITH_INDEX(emp emp_job_idx) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno AND job = 'ANALYST';

Execution Plan
----------------------------------------------------------
Plan hash value: 1902963759

-----------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |     2 |    96 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                         |             |     2 |    96 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |             |     2 |    96 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP         |     2 |    52 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | EMP_JOB_IDX |     2 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                  | PK_DEPT     |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | DEPT        |     1 |    22 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   4 - access("JOB"='ANALYST')
   5 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan

HASH JOIN (PGA)

It picks the small table and then create a hash value based on the joining key column and placed the hash value in a table in memory.It then scans another table and compare the values with the in-memory hash table.A match is a join.The performance depends upon the size of join key and the available memory for the in-memory hash table.

It is only used in case of EQUI-JOIN.

USE_HASH(table1,table2) :-

This hint tells the optimizer to use HASH JOIN.

NO_USE_HASH(table1,table2) :-

This hint tells the optimizer NOT to use HASH JOIN.

SQL> SELECT /*+ USE_HASH(emp,dept) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   308 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   308 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 -  SEL$1 / DEPT@SEL$1
         U -  USE_HASH(emp,dept)

SQL> SELECT /*+ USE_NO_HASH(emp,dept) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   308 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   308 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------

   1 -  SEL$1
         E -  USE_NO_HASH

SORT-MERGE-JOIN (PGA)

Oracle sorts the first table by its join columns,sorts the second table by its join columns and then merges the sorted row source together.As matches are found they are put in into the result-set.This type of join is effective when there is a lack of data selectivity or NL join is insufficient or both the tables are quite large.

USE_MERGE(table1,table2) :-

This hint tells the optimizer to use SORT MERGE JOIN.

NO_USE_MERGE(table1,table2) :-

This hint tells the optimizer NOT to use SORT MERGE JOIN.

SQL> SELECT /*+ USE_MERGE(emp,dept) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   308 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   308 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 -  SEL$1 / DEPT@SEL$1
         U -  USE_MERGE(emp,dept)

SQL> SELECT /*+ NO_USE_MERGE(emp,dept) */ ename,dname FROM emp,dept WHERE emp.deptno = dept.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   308 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   308 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   126 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Share it

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

Oracle Database Programming (SQL & PL/SQL)
Perl Programming
Unix Programming

Understanding ORACLE HINTS For JOIN OPERATIONS In Oracle Database

Share it

Shoumadip Das

Related Topics :

Understanding Global Temporary Table In Oracle

Understanding PACKAGED CURSOR In Oracle Database

Understanding Oracle 12c ACCESSIBLE BY CLAUSE

Leave a Reply Cancel reply