注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

程序员驿站

淘宝店:http://shop106888457.taobao.com

 
 
 

日志

 
 

浅析为何Oracle物化视图对distinct, group by不支持快速刷新  

2015-01-30 14:25:09|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
问题描述:我们知道distinct,group by 不能用于物化视图的快速刷新,而 group by + count 就可以,下面通过简单例子分析一下原因。
实验场景:Oracle 10 / Oracle Linux

一个物化视图定义语句
create materialized view mv_name 
build immediate 
refresh fast on commit 
enable query rewrite 
as select distinct x,y,z from table_name 

物化视图三种刷新方式:complete, fast, force 其中complete 相当于清空当前mv所有的内容,重新执行一次创建语句,所以这种刷新方式不需要物化视图日志; fast是根据mv log更新的内容,增量刷新到mv中;force是个和事老,咱们先fast刷新,如果不支持,咱就complete。 所以如要搞清楚distinct,group by为什么不能应用于快速刷新,就要搞清楚如果不用基础表,光靠物化视图日志和物化视图,能不能完成distinct,group by操作。

我们先看一个正确的创建的物化视图,分析它快速刷新的过程:(所有测试均在HR 用户下完成)
  1. 1. 创建物化视图日志
  2. SQL> select * from t1;

  3.           X          Y          Z  
  4. ---------- ---------- ---------- 
    1.          1          1          1
    2.          1          1          1 
    3.          2          2          2
    4.          2          2          2         
               3          3          3
    5.          3          3          3          
               6          6          6
    6.          6          6          6          
              11         11         11                  
    7.          7          7          7
    8.          7          7          7                    
               8          8          8
    9.          8          8          8    
  SQL> create materialized view log on t1 with sequence, rowid(x,y,z) including new values;

  1. SQL> create materialized view mv_t1 build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) 
  2.      from t1 group by x,y,z;

  3. SQL> select * from mv_t1; 
     
             X          Y          Z   COUNT(*)
    ---------- ---------- ---------- ----------
  4.          1          1          1          2
             3          3          3          2
             6          6          6          2
            11         11         11          1
             5          5          5          2
             7          7          7          2
             2          2          2          2
             8          8          8          2

  5. rows selected.

2. 删除x=1的两列
  1. SQL> delete from t1 where x=1;

  2. rows deleted.

3. 查看物化视图日志

  1. SQL> select * from mlog$_t1;

  2.          X          Y          Z M_ROW$$                        SEQUENCE$$ SNAPTIME$$          D O CHANGE_VECTOR$$
  3. ---------- ---------- ---------- ------------------------------ ---------- ------------------- - - ------------------
  4.          1          1          1 AAACfYAAFAAABATAAK             20002      4000-01-01 00:00:00 D O 00
  5.          1          1          1 AAACfYAAFAAABATAAA             20001      4000-01-01 00:00:00 D O 00

4. 从v$sqlarea视图中抓取Oracle内部是如何执行快速刷新的语句

  1. SQL> commit;

  2. Commit complete.

  3. SQL> ed

  4.   1 select * from
  5.   2 (
  6.   3 select sql_text
  7.   4 from v$sqlarea
  8.   5 where sql_text like '%MV_REFRESH%'
  9.   6 order by buffer_gets desc
  10.   7 )
  11.   8* where rownum < 5
  12.   9 /

  13. SQL_TEXT
  14. ------------------------------------------------------------------------------------------------------------------------

  15. /* MV_REFRESH (UPD) */ UPDATE /*+ BYPASS_UJVC */ (SELECT /*+ NO_MERGE("AV$") */ "SNA$"."COUNT(*)" "C0", "AV$"."D0" FROM
    "HR"."MV_T1" "SNA$", (SELECT   /*+  OPT_ESTIMATE(QUERY_BLOCK MAX=1000)   */ "DLT$0"."X" "GB0", "DLT$0"."Y" "GB1", "DLT$0
    "."Z" "GB2", SUM(-1) "D0" FROM (SELECT  /*+ CARDINALITY(MAS$ 2) */  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."Z", "
    MAS$"."Y", "MAS$"."X"   FROM "HR"."MLOG$_T1" "MAS$"   WHERE "MAS$".SNAPTIME$$ > :1 ) "DLT$0"  GROUP BY "DLT$0"."X","DLT$
    0"."Y","DLT$0"."Z")"AV$" WHERE SYS_OP_MAP_NONNULL("SNA$"."X")=SYS_OP_MAP_NONNULL("AV$"."GB0") AND SYS_OP_MAP_NONNULL("SN
    A$"."Y")=SYS_OP_MAP_NONNULL("AV$"."GB1") AND SYS_OP_MAP_NONNULL("SNA$"."Z")=SYS_OP_MAP_NONNULL("AV$"."GB2")) UV$ SET "C0
    "="C0"+"D0"

  16. /* MV_REFRESH (DEL) */ DELETE FROM \"HR\".\"MV_T1\" \"SNA$\" WHERE \"SNA$\".\"COUNT(*)\"=0

5. 我们发现这个对基础表的删除语句产生了两条刷新语句,整理格式,查看执行计划便于分析:
  1. SQL> explain plan for 

  2. UPDATE /*+ BYPASS_UJVC */ 
  3.  (SELECT /*+ NO_MERGE("AV$") */ "SNA$"."COUNT(*)" "C0", "AV$"."D0" 
  4.   FROM
        "HR"."MV_T1" "SNA$", 
  5.     (SELECT /*+ OPT_ESTIMATE(QUERY_BLOCK MAX=1000) */ "DLT$0"."X" "GB0", "DLT$0"."Y" "GB1", "DLT$0"."Z" "GB2", SUM(-1) "D0" 
  6.      FROM 
  7.         (SELECT  /*+ CARDINALITY(MAS$ 2) */  CHARTOROWID("MAS$"."M_ROW$$") RID$  ,  "MAS$"."Z", "MAS$"."Y","MAS$"."X" 
  8.           FROM 
  9.             "HR"."MLOG$_T1" "MAS$"  WHERE "MAS$".SNAPTIME$$ > :1 
  10.          ) "DLT$0"  
  11.      GROUP BY "DLT$0"."X","DLT$0"."Y", "DLT$0"."Z"
  12.      )"AV$" 
  13.   WHERE 
  14.      SYS_OP_MAP_NONNULL("SNA$"."X")=SYS_OP_MAP_NONNULL("AV$"."GB0") AND 
  15.      SYS_OP_MAP_NONNULL("SNA$"."Y")=SYS_OP_MAP_NONNULL("AV$"."GB1") AND 
  16.      SYS_OP_MAP_NONNULL("SNA$"."Z")=SYS_OP_MAP_NONNULL("AV$"."GB2")
  17.   ) UV$ 

  18. SET "C0"="C0"+"D0"

  19. Explained.
  1. 注释:
  2. -- SNA$ = "HR"."MV_T1"      代表物化视图
  3. -- MAS$ = "HR"."MLOG$_T1"   代表物化视图日志
  4. -- M_ROW$$                  物化视图日志中的列,代表rowid
  1. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

  2. PLAN_TABLE_OUTPUT
  3. -------------------------------------------------------------------------------------------------
  4. Plan hash value: 3886021645

  5. --------------------------------------------------------------------------------------------------
  6. | Id | Operation                         | Name          | Rows | Bytes | Cost (%CPU)| Time |
  7. --------------------------------------------------------------------------------------------------
  8. | 0 | UPDATE STATEMENT                   |               | 1    | 104   | 18 (6)    | 00:00:01 |
  9. | 1 |   UPDATE                           | MV_T1         |      |       |           |          |
  10. | 2 |     NESTED LOOPS                   |               | 1    | 104   | 18 (6)    | 00:00:01 |
  11. | 3 |      VIEW                          |               | 2    | 104   | 17 (6)    | 00:00:01 |
  12. | 4 |       SORT GROUP BY                |               | 2    | 96    | 17 (6)    | 00:00:01 |
  13. |* 5|        TABLE ACCESS FULL           | MLOG$_T1      | 2    | 96    | 16 (0)    | 00:00:01 |
  14. | 6 |      MAT_VIEW ACCESS BY INDEX ROWID| MV_T1         | 1    | 52    | 1 (0)     | 00:00:01 |
  15. |* 7|       INDEX UNIQUE SCAN            | I_SNAP$_MV_T1 | 1    |       | 0 (0)     | 00:00:01 |
  16. --------------------------------------------------------------------------------------------------

  17. Predicate Information (identified by operation id):
  18. ---------------------------------------------------

  19.    5 - filter("MAS$"."SNAPTIME$$">:1)
  20.    7 - access(SYS_OP_MAP_NONNULL("X")=SYS_OP_MAP_NONNULL("AV$"."GB0") AND
  21.               SYS_OP_MAP_NONNULL("Y")=SYS_OP_MAP_NONNULL("AV$"."GB1") AND
  22.               SYS_OP_MAP_NONNULL("Z")=SYS_OP_MAP_NONNULL("AV$"."GB2"))

  23. Note
  24. -----
  25.    - dynamic sampling used for this statement

  26. 26 rows selected.

SQL> explain plan for /* MV_REFRESH (DEL) */ DELETE FROM "HR"."MV_T1" "SNA$" WHERE "SNA$"."COUNT(*)"=0;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
Plan hash value: 583344350

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | DELETE STATEMENT      |       |     1 |    52 |    19   (0)| 00:00:01 |
|   1 |  DELETE               | MV_T1 |       |       |            |          |
|*  2 |   MAT_VIEW ACCESS FULL| MV_T1 |     1 |    52 |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------------


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

   2 - filter("SNA$"."COUNT(*)"=0)

Note
-----
   - dynamic sampling used for this statement

18 rows selected.

6. 语句分析结论:

经过对两条刷新语句的分析我们可以知道,步骤2 中的 delete 基础表数据的语句,对应的物化视图更新的SQL 有两个,
第一条复杂的update语句的作用是,从物化视图中的count字段减去delete操作删掉基础表的行数(从物化视图日志中得出),
如果相减等于0,就是说表中所有等于1的行已全部被删除,于是就产生了第二条更新,就是从物化视图中删除这条记录。 
从这我们可以看出,如果没有count(*)字段,单凭现有的物化视图,以及物化视图日志,只知道删了几个,没有办法知道删除
之前的基础表相同的数据到底有几个,比如基础表原有 x=3,y=3,z=3 的行有3个,此时若物化视图没有count(*) 列,那么它将
只有一条3,3,3的记录。若delete语句对基础表删除了所有3,3,3的数据,物日志中将有3条记录,而物化视图因为缺少总共的条
目记录,将无法知道该条目是否应该从视图中删除





补充:以下是对第一条刷新操作SQL的拆分执行:

  1. SQL> create table AV$ as SELECT /*+ CARDINALITY(MAS$ 2) */ CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."Z", "MAS$"."Y","MAS$"."X"
  2.         FROM "HR"."MLOG$_T1" "MAS$" 2 ;

  3. RID$               Z Y X
  4. ------------------ - --------- ---------- ----------
  5. AAACfYAAFAAABATAAC 3 3 3
  6. AAACfYAAFAAABATAAM 3 3 3

  7. SQL> select * from AV$
  8.   
  9.      GB0 GB1 GB2 D0
  10.     ---------- ---------- ---------- ----------
  11.      3 3 3 -2

  12. SQL> 

  13.   1 SELECT /*+ NO_MERGE(\"AV$\") */ SNA$.\"COUNT(*)\" \"C0\", \"AV$\".\"D0\"
  14.   2 FROM
  15.   3 \"HR\".\"T3\" \"SNA$\", \"AV$\"
  16.   4 WHERE
  17.   5 SYS_OP_MAP_NONNULL(\"SNA$\".\"X\")=SYS_OP_MAP_NONNULL(\"AV$\".\"GB0\") AND
  18.   6 SYS_OP_MAP_NONNULL(\"SNA$\".\"Y\")=SYS_OP_MAP_NONNULL(\"AV$\".\"GB1\") AND
  19.   7* SYS_OP_MAP_NONNULL(\"SNA$\".\"Z\")=SYS_OP_MAP_NONNULL(\"AV$\".\"GB2\")
  20.   8 
  21. SQL> /


  22.         C0 D0
  23. ---------- ----------
  24.          2 -2

结论:从上面的实验可以得出,对于delete 操作,物化视图语句中的distinct, group by 不查询基础表的情况下,通过物化视图本身以及物化视图日志是无法
进行刷新的,就是无法进行快速刷新功能。 对于其它对基础表的DML操作,可以用相同的方法加以测试。


  评论这张
 
阅读(332)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017