MERGE 与NO

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)