The New Home of Oracle-wiki.net
All things oracle...
A website for IT Professionals involved with Oracle database.
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
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;
------------------------------------------------------------------------------------
| 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
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'
);
--------------------------------------------------------------------------------------------------------------
| 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!!
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