no_merge/merge vs no_unnest/unnest

I sometimes get confused about the difference between (no_)merge and(no_)unnest. I just do some test here to make the difference clearly.

The original sql and its plan are below. I’ll hint the sql with no_merge andno_unnest. You will find the difference quickly.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

select * from emp where deptno in (select deptno from dept where dname='SALES');   ---------------------------------------------------------- Plan hash value: 844388907   ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |     4 |   208 |     6  (17)| 00:02:09 | |   1 |  MERGE JOIN                  |         |     4 |   208 |     6  (17)| 00:02:09 | |*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     2   (0)| 00:00:43 | |   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:22 | |*  4 |   SORT JOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 | |   5 |    TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 | ----------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("DNAME"='SALES')    4 - access("DEPTNO"="DEPTNO")        filter("DEPTNO"="DEPTNO")

I try the hint no_merge in order to avoid merging the subquery. This obviously don’t work.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SQL> select * from emp where deptno in (select /*+ no_merge */ deptno from dept where dname='SALES');   ---------------------------------------------------------- Plan hash value: 844388907   ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |     4 |   208 |     6  (17)| 00:02:09 | |   1 |  MERGE JOIN                  |         |     4 |   208 |     6  (17)| 00:02:09 | |*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     2   (0)| 00:00:43 | |   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:22 | |*  4 |   SORT JOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 | |   5 |    TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 | ----------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("DNAME"='SALES')    4 - access("DEPTNO"="DEPTNO")        filter("DEPTNO"="DEPTNO")

Then I try the hint no_unnest. It works now. That means (no_)unest works only in the where clause. So I guess (no_)merge is only suitable after the from.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SQL> select * from emp where deptno in (select /*+ no_unnest */ deptno from dept where dname='SALES');   ---------------------------------------------------------- Plan hash value: 2809975276   ---------------------------------------------------------------------------------------- | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |         |     4 |   156 |     6   (0)| 00:02:09 | |*  1 |  FILTER              |         |       |       |        |          | |   2 |   TABLE ACCESS FULL      | EMP     |    13 |   507 |     3   (0)| 00:01:05 | |*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:22 | |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 | ----------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE           "DEPTNO"=:B1 AND "DNAME"='SALES'))    3 - filter("DNAME"='SALES')    4 - access("DEPTNO"=:B1)

I do another test with the original sql below:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SQL> select * from emp, (select * from dept where dname = 'SALES') dept where dept.deptno = emp.deptno;   ---------------------------------------------------------- Plan hash value: 844388907   ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |     4 |   236 |     6  (17)| 00:02:09 | |   1 |  MERGE JOIN                  |         |     4 |   236 |     6  (17)| 00:02:09 | |*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     2   (0)| 00:00:43 | |   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:22 | |*  4 |   SORT JOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 | |   5 |    TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 | ----------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("DNAME"='SALES')    4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")        filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

you will see the no_unest hint doesn’t work.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

SQL> select * from emp, (select /*+ no_unnest */ * from dept where dname = 'SALES') dept where dept.deptno = emp.deptno;   ---------------------------------------------------------- Plan hash value: 844388907   ---------------------------------------------------------------------------------------- | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |         |     4 |   236 |     6  (17)| 00:02:09 | |   1 |  MERGE JOIN                  |         |     4 |   236 |     6  (17)| 00:02:09 | |*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     2   (0)| 00:00:43 | |   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:22 | |*  4 |   SORT JOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 | |   5 |    TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 | ----------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("DNAME"='SALES')    4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")        filter("DEPT"."DEPTNO"="EMP"."DEPTNO")

Now I hint the original sql with the hint no_merge. It works. That’s it!

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL> select * from emp, (select /*+ no_merge */ * from dept where dname = 'SALES') dept where dept.deptno = emp.deptno;   ---------------------------------------------------------- Plan hash value: 2910064727   ---------------------------------------------------------------------------- | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |      |     4 |   276 |     7  (15)| 00:02:19 | |*  1 |  HASH JOIN          |      |     4 |   276 |     7  (15)| 00:02:19 | |   2 |   VIEW              |      |     1 |    30 |     3   (0)| 00:01:05 | |*  3 |    TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:01:05 | |   4 |   TABLE ACCESS FULL | EMP  |    13 |   507 |     3   (0)| 00:01:05 | ----------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")    3 - filter("DNAME"='SALES')