NO_MERGE视图合并案例

最新推荐文章于 2024-07-31 11:27:06 发布

指尖上的程序

最新推荐文章于 2024-07-31 11:27:06 发布

阅读量2.6k

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE TURNNING

本文链接：https://blog.youkuaiyun.com/xiaoxing1521025/article/details/17081805

ORACLE TURNNING 专栏收录该内容

53 篇文章

订阅专栏

本文探讨了Oracle数据库中的NO_MERGE提示符，用于禁止查询优化器合并可合并的视图。通过示例解释了如何使用NO_MERGE在特定查询中避免视图合并，从而影响查询计划和性能。内容包括一个带有NO_MERGE的查询示例，展示了其在窗口排序、哈希连接和索引扫描等操作中的应用。

/*+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;
Plan hash value: 1516306995

---------------------------------------------------------------------------------------------------------
| Id | Operation                      | Name                   | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        | 1364 |   454K| 52803   (1)| 00:10:34 |
|* 1 | VIEW                          |                        | 1364 |   454K| 52803   (1)| 00:10:34 |
|* 2 |   WINDOW SORT PUSHED RANK      |                        | 1364 |    98K| 52803   (1)| 00:10:34 |
|* 3 |    HASH JOIN                   |                        | 1364 |    98K| 52802   (1)| 00:10:34 |
|* 4 |     INDEX FAST FULL SCAN       | PK_FA_DOCANALYSTRELA   | 1364 | 15004 |   598   (3)| 00:00:08 |
|   5 |     TABLE ACCESS BY INDEX ROWID| DOC_RESEARCHREPORTCORE | 71696 | 4410K| 52204   (1)| 00:10:27 |
|* 6 |      INDEX RANGE SCAN          | IND_DOC_RSCHDR7        | 71696 |       |   288   (1)| 00:00:04 |
---------------------------------------------------------------------------------------------------------

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

   1 - filter("ROWN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "FD"."ANALYSTID" ORDER BY
              INTERNAL_FUNCTION("DR"."DOCTIME") DESC )<=1)
   3 - access("FD"."DOCID"="DR"."OBJID")
   4 - filter("FD"."ANALYSTID"=115733 OR "FD"."ANALYSTID"=122765 OR "FD"."ANALYSTID"=127452 OR
              "FD"."ANALYSTID"=127458 OR "FD"."ANALYSTID"=127459 OR "FD"."ANALYSTID"=127614 OR
              "FD"."ANALYSTID"=128072 OR "FD"."ANALYSTID"=128175 OR "FD"."ANALYSTID"=128207 OR
              "FD"."ANALYSTID"=128358)
   6 - access("DR"."DOCTIME">SYSDATE@!-90)