NO_UNNEST hint, query optimization

There are multiple useful hints, that are changing Oracle execution plan for some SQL statement and reduces  the cost. One of them is NO_UNNEST hint.

First of all, let’s discuss briefly what UNNEST hint does.

UNNEST hint “Instructs the optimizer to unnest and merge the body of the subquery into the body of the query block that contains it, allowing the optimizer to consider them together when evaluating access paths and joins.”

For example, if we have the query with inline view like that:

select *
from   hr.employees outer
where  outer.salary > (
              select avg(inner.salary)
              from   hr.employees inner
              where  inner.department_id = outer.department_id
       );

What UNNEST hint actually does, is the following:

select *
from   hr.employees outer,
       (
              select department_id, avg(salary) avg_sal
              from   hr.employees
              group by department_id
       )      inner
Where
       outer.department_id = inner.department_id
and    outer.salary > inner.avg_sal;

Now, when we already know what UNNEST hint does. Let’s see how optimizes the sql statement its opposite NO_UNNEST hint.

–Original SQL Statement

SELECT /*+ index(v1.table1 table1_IX1) */
      v1.col1,
      v1.col2,
      v1.col3,
      v1.col4,
      v1.col5
FROM VIEW1 v1
WHERE v1.code = :B1
AND v1.ID = NVL(NULL, v1.ID)
AND v1.ID IN
             (SELECT v2.sid
              FROM VIEW2 v2                 
              WHERE 'N' = 'N'
              AND v2.Key1 = NVL(NULL, Key1)
              AND NVL(NULL, Active_Flag) = Active_Flag
              AND NVL(NULL, Inform_Flag) = Inform_Flag
              OR('Y' = 'Y' AND :b2 = KEY1 AND Active_Flag = 'Y')
             )

–Explain Plan

--------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |     1 |   244
|   1 |  HASH JOIN SEMI                   |                     |     1 |   244
|   2 |   NESTED LOOPS OUTER              |                     |     1 |   231
|   3 |    TABLE ACCESS BY INDEX ROWID    | TABLE1              |     1 |   110
|   4 |     INDEX RANGE SCAN              | TABLE1_IX1          |     2 |
|   5 |    TABLE ACCESS BY INDEX ROWID    | TABLE2              |     1 |   121
|   6 |     INDEX UNIQUE SCAN             | TABLE2_PK           |     1 |
|   7 |   VIEW                            | VW_NSO_1            |     2 |    26
|   8 |    CONCATENATION                  |                     |       |
|   9 |     TABLE ACCESS BY INDEX ROWID   | TABLE3              |     1 |    21
|  10 |      NESTED LOOPS                 |                     |     1 |    49
|  11 |       NESTED LOOPS                |                     |     1 |    28
|  12 |        TABLE ACCESS BY INDEX ROWID| TABLE4              |     1 |    18
|  13 |         INDEX UNIQUE SCAN         | TABLE4_PK           |     1 |
|  14 |        TABLE ACCESS BY INDEX ROWID| TABLE5              |     1 |    10
|  15 |         INDEX RANGE SCAN          | TABLE5_PK           |     1 |
|  16 |       INDEX RANGE SCAN            | TABLE1_IX1          |     1 |
|  17 |     TABLE ACCESS BY INDEX ROWID   | TABLE5              |     1 |    10
|  18 |      NESTED LOOPS                 |                     |     1 |    49
|  19 |       NESTED LOOPS                |                     |     1 |    39
|  20 |        TABLE ACCESS FULL          | TABLE3              |  4559 | 95739
|  21 |        TABLE ACCESS BY INDEX ROWID| TABLE4              |     1 |    18
|  22 |         INDEX UNIQUE SCAN         | TABLE4_PK           |     1 |
|  23 |       INDEX RANGE SCAN            | TABLE5_PK           |     1 |
--------------------------------------------------------------------------------

COST IS:  9192

–With hint

SELECT /*+ index(v1.table1 table1_IX1) NO_UNNEST(@sq1)*/
      v1.col1,
      v1.col2,
      v1.col3,
      v1.col4,
      v1.col5
FROM VIEW1 v1
WHERE v1.code = :B1
AND v1.ID = NVL(NULL, v1.ID)
AND v1.ID IN
             (SELECT /*+ qb_name(sq1)*/v2.sid
              FROM VIEW2 v2
              WHERE 'N' = 'N'
              AND v2.Key1 = NVL(NULL, Key1)
              AND NVL(NULL, Active_Flag) = Active_Flag
              AND NVL(NULL, Inform_Flag) = Inform_Flag
              OR('Y' = 'Y' AND :b2 = KEY1 AND Active_Flag = 'Y')
             )

Note: I used query naming qb_name.

–Explain plan

--------------------------------------------------------------------------------
| Id  | Operation                       | Name                 | Rows  | Bytes |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |     1 |   231 |
|   1 |  FILTER                         |                      |       |       |
|   2 |   NESTED LOOPS OUTER            |                      |     1 |   231 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | TABLE1               |     1 |   110 |
|   4 |     INDEX RANGE SCAN            | TABLE1_IX1           |     2 |       |
|   5 |    TABLE ACCESS BY INDEX ROWID  | TABLE2               |     1 |   121 |
|   6 |     INDEX UNIQUE SCAN           | TABLE2_PK            |     1 |       |
|   7 |   TABLE ACCESS BY INDEX ROWID   | TABLE5               |     1 |    10 |
|   8 |    NESTED LOOPS                 |                      |     1 |    49 |
|   9 |     NESTED LOOPS                |                      |     1 |    39 |
|  10 |      TABLE ACCESS BY INDEX ROWID| TABLE3               |     3 |    63 |
|  11 |       INDEX RANGE SCAN          | TABLE3_IX1           |     3 |       |
|  12 |      TABLE ACCESS BY INDEX ROWID| TABLE4               |     1 |    18 |
|  13 |       INDEX UNIQUE SCAN         | TABLE4_PK            |     1 |       |
|  14 |     INDEX RANGE SCAN            | TABLE5_PK            |     1 |       |

COST IS: 16

I hope it was helpful…