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

最新推荐文章于 2022-08-16 09:41:27 发布

csdnw1129

最新推荐文章于 2022-08-16 09:41:27 发布

阅读量450

点赞数

文章标签：数据库操作系统

问题描述：我们知道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. 创建物化视图日志
SQL> select * from t1;
X Y Z
---------- ---------- ----------
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;

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

X Y Z COUNT(*)
---------- ---------- ---------- ----------
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 rows selected.

2. 删除x=1的两列

SQL> delete from t1 where x=1;
2 rows deleted.

3. 查看物化视图日志

SQL> select * from mlog$_t1;
X Y Z M_ROW$$ SEQUENCE$$ SNAPTIME$$ D O CHANGE_VECTOR$$
---------- ---------- ---------- ------------------------------ ---------- ------------------- - - ------------------
1 1 1 AAACfYAAFAAABATAAK 20002 4000-01-01 00:00:00 D O 00
1 1 1 AAACfYAAFAAABATAAA 20001 4000-01-01 00:00:00 D O 00

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

SQL> commit;
Commit complete.
SQL> ed
1 select * from
2 (
3 select sql_text
4 from v$sqlarea
5 where sql_text like '%MV_REFRESH%'
6 order by buffer_gets desc
7 )
8* where rownum < 5
9 /
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
/* 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"
/* MV_REFRESH (DEL) */ DELETE FROM \"HR\".\"MV_T1\" \"SNA$\" WHERE \"SNA$\".\"COUNT(*)\"=0

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

SQL> explain plan for
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("SNA$"."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"
Explained.

注释：
-- SNA$ = "HR"."MV_T1" 代表物化视图
-- MAS$ = "HR"."MLOG$_T1" 代表物化视图日志
-- M_ROW$$ 物化视图日志中的列，代表rowid

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 3886021645
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 104 | 18 (6) | 00:00:01 |
| 1 | UPDATE | MV_T1 | | | | |
| 2 | NESTED LOOPS | | 1 | 104 | 18 (6) | 00:00:01 |
| 3 | VIEW | | 2 | 104 | 17 (6) | 00:00:01 |
| 4 | SORT GROUP BY | | 2 | 96 | 17 (6) | 00:00:01 |
|* 5| TABLE ACCESS FULL | MLOG$_T1 | 2 | 96 | 16 (0) | 00:00:01 |
| 6 | MAT_VIEW ACCESS BY INDEX ROWID| MV_T1 | 1 | 52 | 1 (0) | 00:00:01 |
|* 7| INDEX UNIQUE SCAN | I_SNAP$_MV_T1 | 1 | | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("MAS$"."SNAPTIME$$">:1)
7 - access(SYS_OP_MAP_NONNULL("X")=SYS_OP_MAP_NONNULL("AV$"."GB0") AND
SYS_OP_MAP_NONNULL("Y")=SYS_OP_MAP_NONNULL("AV$"."GB1") AND
SYS_OP_MAP_NONNULL("Z")=SYS_OP_MAP_NONNULL("AV$"."GB2"))
Note
-----
- dynamic sampling used for this statement
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的拆分执行：

SQL> create table AV$ as SELECT /*+ CARDINALITY(MAS$ 2) */ CHARTOROWID("MAS$"."M_ROW$$") RID$ , "MAS$"."Z", "MAS$"."Y","MAS$"."X"
FROM "HR"."MLOG$_T1" "MAS$" 2 ;
RID$ Z Y X
------------------ - --------- ---------- ----------
AAACfYAAFAAABATAAC 3 3 3
AAACfYAAFAAABATAAM 3 3 3
SQL> select * from AV$
GB0 GB1 GB2 D0
---------- ---------- ---------- ----------
3 3 3 -2
SQL>
1 SELECT /*+ NO_MERGE(\"AV$\") */ SNA$.\"COUNT(*)\" \"C0\", \"AV$\".\"D0\"
2 FROM
3 \"HR\".\"T3\" \"SNA$\", \"AV$\"
4 WHERE
5 SYS_OP_MAP_NONNULL(\"SNA$\".\"X\")=SYS_OP_MAP_NONNULL(\"AV$\".\"GB0\") AND
6 SYS_OP_MAP_NONNULL(\"SNA$\".\"Y\")=SYS_OP_MAP_NONNULL(\"AV$\".\"GB1\") AND
7* SYS_OP_MAP_NONNULL(\"SNA$\".\"Z\")=SYS_OP_MAP_NONNULL(\"AV$\".\"GB2\")
8
SQL> /
C0 D0
---------- ----------
2 -2