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* fromemp wheredeptno in(selectdeptno fromdept wheredname='SALES');

 

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

Plan hash value: 844388907

 

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

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

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

|   0 |SELECT STATEMENT             |         |     4 |   208 |     6  (17)| 00:02:09 |

|   1 |  MERGEJOIN                  |         |     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 |   SORTJOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 |

|   5 |   TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 |

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

 

Predicate Information (identifiedby 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 fromdept wheredname='SALES');

 

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

Plan hash value: 844388907

 

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

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

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

|   0 |SELECT STATEMENT             |         |     4 |   208 |     6  (17)| 00:02:09 |

|   1 |  MERGEJOIN                  |         |     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 |   SORTJOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 |

|   5 |   TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 |

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

 

Predicate Information (identifiedby 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 fromdept wheredname='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 (identifiedby operation id):

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

 

   1 - filter( EXISTS (SELECT/*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT" WHERE

          "DEPTNO"=:B1AND "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') deptwhere 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 |  MERGEJOIN                  |         |     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 |   SORTJOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 |

|   5 |   TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 |

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

 

Predicate Information (identifiedby 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 */ * fromdept wheredname = 'SALES') deptwhere 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 |  MERGEJOIN                  |         |     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 |   SORTJOIN                  |         |    13 |   507 |     4  (25)| 00:01:26 |

|   5 |   TABLE ACCESS FULL         | EMP     |    13 |   507 |     3   (0)| 00:01:05 |

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

 

Predicate Information (identifiedby 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 */ * fromdept wheredname = 'SALES') deptwhere 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 |  HASHJOIN          |      |     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 (identifiedby operation id):

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

 

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

   3 - filter("DNAME"='SALES')