关闭

NO_MERGE视图合并案例

723人阅读 评论(0) 收藏 举报
分类:
/*+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)

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:390684次
    • 积分:6400
    • 等级:
    • 排名:第3782名
    • 原创:228篇
    • 转载:275篇
    • 译文:24篇
    • 评论:7条
    最新评论