MERGE 与NO_MERGE

19. /*+ MERGE(TABLE) */  
  能够对视图的各个查询进行相应的合并.  
  例如:  
  SELECT /*+ MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO  
  ,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO  
  AND A.SAL>V.AVG_SAL;  
20. /*+NO_MERGE(TABLE)*/  
  对于有可合并的视图不再合并.  
  例如:  
  SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND

A.SAL>V.AVG_SAL;  
  21. /*+ORDERED*/  



 SELECT /*+no_merge(XJ) */
              (SELECT DISTINCT T.ZUOY_STAFF FROM VIW_YW_ZYRYJL T WHERE T.DANJ_NO=XJ.DANJ_NO AND T.HANGHAO=XJ.HANGHAO AND T.CAOZJS_ID IN(
               SELECT A.JIAOS_ID FROM KH_JC_JSZD A WHERE A.JIAOS_NAME IN('拆零拣货员','整件拣货员'))) ZUOY_STAFF,
               XJ.DANJ_NO,
               SP.SHANGP_NO,
               SUM(XJ.SHIJ_NUM) ZSL,
               COUNT(DISTINCT XJ.DANJ_NO||XJ.HANGHAO) TMS,
               COUNT(DISTINCT XJ.SHANGP_ID) PGS,
               ROUND(SUM(XJ.SHIJ_NUM / SP.BAOZ_NUM),2) JS
          FROM  JC_SPZL SP,VIW_CK_KPD_HZ ZY, VIW_YW_XJZL XJ
         WHERE ZY.DANJ_NO = XJ.DANJ_NO
           AND SP.SHANGP_ID = xj.SHANGP_ID
           AND XJ.YEW_TYPE IN ('2', '3')
           AND XJ.KUB = 'LHK'
           AND XJ.HUOW_ID <> '冲'
       --    and  TRUNC(cast(SHENGCHEN_TIME AS DATE)) >= TRUNC(TO_DATE('2016-06-22' ,'yyyy-mm-dd'))
        --   AND  TRUNC(cast(SHENGCHEN_TIME AS DATE)) <  TRUNC(TO_DATE('2016-06-22' ,'yyyy-mm-dd') + 1)
          AND cast(XJ.SHENGCHEN_TIME AS DATE) >= TRUNC(TO_DATE('2016-06-01' ,'yyyy-mm-dd'))
           AND cast(XJ.SHENGCHEN_TIME AS DATE) <  TRUNC(TO_DATE('2016-06-22' ,'yyyy-mm-dd') + 1)
           AND XJ.SHIJ_NUM > 0
           AND SUBSTR(SP.SHANGP_NO,1,1) <>'P'
           AND SUBSTR(SP.SHANGP_NO,1,1)<>'Z'
         GROUP BY
                  XJ.DANJ_NO,
                  XJ.PINGX_NO,
                  XJ.HANGHAO,
                  SP.SHANGP_NO,
                  XJ.LOT


qn@RAC> select b.a,v.x from b b,( select a,count(1) x  from a  group by a ) v where b.a=v.a and b.a=1
  2  /

--优化器选择为NO_MERGE(v),HASH GROUP BY 的视图里操作,谓词推入成功;
NO_MERGE 表示视图不合并,不展开

Execution Plan
----------------------------------------------------------
Plan hash value: 1009994850

-----------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |      4 |    156 |      6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |        |      4 |    156 |      6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | B    |      1 |     13 |      3   (0)| 00:00:01 |
|   3 |   VIEW             |        |      4 |    104 |      3   (0)| 00:00:01 |
|   4 |    HASH GROUP BY     |        |      4 |     52 |      3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL| A    |      4 |     52 |      3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - access("B"."A"="V"."A")
   2 - filter("B"."A"=1)
   5 - filter("A"=1)

Note
-----
   - dynamic sampling used for this statement (level=2)
   


--强制为MERGE视图展开，b与v合并在一起。 HASH GROUP BY 在视图外操作,谓词推入成功
qn@RAC> select /*+ MERGE(v) */ b.a,v.x from b b,( select a,count(1) x  from a  group by a ) v where b.a=v.a and b.a=1

Execution Plan
----------------------------------------------------------
Plan hash value: 583771949

----------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time       |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     4 |   152 |     7  (15)| 00:00:01 |
|   1 |  HASH GROUP BY        |       |     4 |   152 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN        |       |     4 |   152 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| B    |     1 |    25 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| A    |     4 |    52 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("B"."A"="A")
   3 - filter("B"."A"=1)
   4 - filter("A"=1)
小结:
   MERGE与NO_MERGE并不影响谓词推入
   MERGE 是什么与视图合并关联
   NO_MERGE 是视图做合并关联





MERGE才是展开视图并做关联
qn@RAC> select  b.a,count(1) from  b b,a a where a.a=b.a and b.a=1 group by b.a
  2  /


Execution Plan
----------------------------------------------------------
Plan hash value: 759381190

-----------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |      4 |    104 |      6   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT|        |      4 |    104 |      6   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |        |      4 |    104 |      6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL | B    |      1 |     13 |      3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | A    |      4 |     52 |      3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   2 - access("A"."A"="B"."A")
   3 - filter("B"."A"=1)
   4 - filter("A"."A"=1)


qn@RAC> select /*+ MERGE(v)*/ b.a,v.x from b b,( select a,count(1) x  from a  group by a ) v where b.a=v.a and b.a=1  2  
Execution Plan
----------------------------------------------------------
Plan hash value: 583771949

----------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time       |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     4 |   152 |     7  (15)| 00:00:01 |
|   1 |  HASH GROUP BY        |       |     4 |   152 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN        |       |     4 |   152 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| B    |     1 |    25 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| A    |     4 |    52 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."A"="A")
   3 - filter("B"."A"=1)
   4 - filter("A"=1)