merge no_merge

select /*+ merge(a) */ a.ct, b.dname
  from (select deptno, count(*) as ct from emp group by deptno) a, dept b
  3   where b.deptno = a.deptno;
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 2708255165

-----------------------------------------------------------------------------------------
| Id  | Operation		      | Name	| Rows	| Bytes | Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	      | 	|     9 |   207 |     7  (29)| 00:00:01 |
|   1 |  HASH GROUP BY		      | 	|     9 |   207 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN		      | 	|    14 |   322 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT	|     4 |    80 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN	      | PK_DEPT |     4 |	|     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN		      | 	|    14 |    42 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL	      | EMP	|    14 |    42 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("B"."DEPTNO"="DEPTNO")
       filter("B"."DEPTNO"="DEPTNO")

select /*+ no_merge(a) */ a.ct, b.dname
  from (select deptno, count(*) as ct from emp group by deptno) a, dept b
  3   where b.deptno = a.deptno;
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 2992795152

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |     3 |   117 |     7	(29)| 00:00:01 |
|   1 |  MERGE JOIN		     |	       |     3 |   117 |     7	(29)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2	 (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN	     | PK_DEPT |     4 |       |     1	 (0)| 00:00:01 |
|*  4 |   SORT JOIN		     |	       |     3 |    78 |     5	(40)| 00:00:01 |
|   5 |    VIEW 		     |	       |     3 |    78 |     4	(25)| 00:00:01 |
|   6 |     HASH GROUP BY	     |	       |     3 |     9 |     4	(25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL	     | EMP     |    14 |    42 |     3	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."DEPTNO"="A"."DEPTNO")
       filter("B"."DEPTNO"="A"."DEPTNO")