ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 浅析为何Oracle物化视图对distinct, group by不支持快速刷新

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

Oracle 作者：xiaoxiaowm 时间：2014-08-16 14:48:56 0 删除 编辑
问题描述：我们知道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;

  4.           X          Y          Z  
  5. ---------- ---------- ---------- 
    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;

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

  5. SQL> select * from mv_t1; 
     
             X          Y          Z   COUNT(*)
    ---------- ---------- ---------- ----------
  6.          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

  8. 8 rows selected.

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

  3. 2 rows deleted.

3. 查看物化视图日志

  2. SQL> select * from mlog$_t1;

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

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

  2. SQL> commit;

  4. Commit complete.

  6. SQL> ed

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

  18. SQL_TEXT
  19. ------------------------------------------------------------------------------------------------------------------------

  21. /* 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"

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

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

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

  20. SET "C0"="C0"+"D0"

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

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

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

  20. Predicate Information (identified by operation id):
  21. ---------------------------------------------------

  23.    5 - filter("MAS$"."SNAPTIME$$">:1)
  24.    7 - access(SYS_OP_MAP_NONNULL("X")=SYS_OP_MAP_NONNULL("AV$"."GB0") AND
  25.               SYS_OP_MAP_NONNULL("Y")=SYS_OP_MAP_NONNULL("AV$"."GB1") AND
  26.               SYS_OP_MAP_NONNULL("Z")=SYS_OP_MAP_NONNULL("AV$"."GB2"))

  28. Note
  29. -----
  30.    - dynamic sampling used for this statement

  32. 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 ;

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

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

  15. SQL> 

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


  28.         C0 D0
  29. ---------- ----------
  30.          2 -2

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








来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24070544/viewspace-1252353/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 没有了~
下一篇： UPDATE加SELECT语句分析
请登录后发表评论 登录
全部评论
xiaoxiaowm

注册时间：2014-06-09

  • 博文量
    5
  • 访问量
    12111

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593