UNNEST and NO_UNNEST Hints

Introduction

The UNNEST hint in Oracle is used to prompt the optimizer to convert a subquery into join.  A process known as unnesting. By default, Oracle may choose whether or not to perform this transformation based on the subquery’s structure and cost based optimization analysis. With the UNNEST hint, you can explicitly request this transformation, overriding the optimizer's default behavior. 

Unnesting a subquery can allow Oracle to explore join methods, apply join predicates earlier, and avoid treating the subquery as a separate, isolated unit. This can lead to better execution plans and performance improvements in many cases. 

There are three main categories of sub-queries where unnesting can take place

• Inline Views - sub-queries in the FROM clause.

• Scalar Sub-queries – sub-queries that return a single value, typically used in the SELECT or WHERE clause.

• Correlated Sub-queries – sub-queries that reference columns from the outer query.

Lets look at asome examples

Using the UNNEST hint

Example for an Inline view 

The following query is an example of how the UNNEST hint works for an Inline view.  It also includes an example query plan

SELECT /*+ UNNEST(d) */

       e.employee_id, d.department_name

FROM   employees e,

       (SELECT department_id, department_name

        FROM   departments

        WHERE  location_id = 1000) d

WHERE  e.department_id = d.department_id;

Example Plan

------------------------------------------------------------------------------------

| Id | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)   |

------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT             |              |       |       |   12  (100)   |

|  1 |  HASH JOIN                   |              |   100 |  5000 |   12   (9)    |

|  2 |   TABLE ACCESS FULL          | EMPLOYEES    |   200 |  6000 |    5   (0)    |

|  3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS  |    5  |   75  |    3   (0)    |

|  4 |    INDEX RANGE SCAN          | DEPT_LOC_IDX |    5  |       |    2   (0)    |

------------------------------------------------------------------------------------

Explanation of the Plan:

• Operation 1: HASH JOIN
  This indicates that the optimizer has flattened the inline view and turned it into a join between employees and departments.

• Operation 2: TABLE ACCESS FULL on EMPLOYEES
  The table is scanned fully—this depends on available indexes and statistics.

• Operation 3 and 4: Access on DEPARTMENTS

Example for an Inline view 

The following query is an example of how the UNNEST hint works for an Scalar Sub-Query.  It also includes an example query plan

SELECT /*+ UNNEST */ employee_id, first_name, last_name

FROM employees

WHERE department_id = (

    SELECT department_id

    FROM departments

    WHERE department_name = 'Sales'

);

Example Plan

--------------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |               |     1 |   106 |     4   (0)| 00:00:01 |       |       |

|* 1 |  HASH JOIN                     |               |     1 |   106 |     4   (0)| 00:00:01 |       |       |

|* 2 |   TABLE ACCESS FULL            | DEPARTMENTS   |     1 |    28 |     2   (0)| 00:00:01 |       |       |

|* 3 |   TABLE ACCESS FULL            | EMPLOYEES     |   107 |  8346 |     2   (0)| 00:00:01 |       |       |

--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")

   2 - filter("DEPARTMENT_NAME"='Sales')

   3 - filter("EMPLOYEES"."DEPARTMENT_ID" IS NOT NULL)

Note

-----

   - 'UNNEST' hint has been applied, resulting in a hash join.

   - The subquery has been merged into the main query.

   - The execution plan reflects the optimizer's choice to use full table scans

Explanation of the Plan

• Operation 0: This is the root of the query plan.

• Operation 1: Because of the /*+ UNNEST */ hint, Oracle has merged the subquery into the main query and is using a hash join. This means Oracle will build a hash table from the smaller dataset (likely DEPARTMENTS after filtering) and then probe that table with the larger dataset (EMPLOYEES).

• Operation 2: Oracle is performing a full table scan on the DEPARTMENTS table to find the department_id for the 'Sales' department.

• Operation 3: Oracle is performing a full table scan on the EMPLOYEES table to find all employees.

Predicate Information

• • Predicate 1 indicates the join condition between the EMPLOYEES and DEPARTMENTS tables.

  • Predicate 2 shows the filter applied to the DEPARTMENTS table (department_name = 'Sales').

  • Predicate 3 shows a filter applied to the employees table, to ensure that the department_id is not null.

Note: Its important to note that in all of these examples, explain plans in the real world are often much more complex.  Also, using the hint does not guarantee the optimizer will chooses these paths.  The optimizer will still look at the overall cost to decide which plan is more optimal.  And of course, test, test, test!!

NO_UNNEST Hint

The NO_UNNEST hint tells the optimizer not to  flatten or unnest a sub-query even if the optimizer thinks doing so might improve performance. 

Further Reading

• You can find more information on using the hint in the documentation 

Published on 

Published 4th April 2025