問題描述：我們知道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

結論：從上面的實驗可以得出，對於delete 操作，物化檢視語句中的distinct, group by 不查詢基礎表的情況下，透過物化檢視本身以及物化檢視日誌是無法
進行重新整理的，就是無法進行快速重新整理功能。對於其它對基礎表的DML操作，可以用相同的方法加以測試。

淺析為何Oracle物化檢視對distinct, group by不支援快速重新整理

相關文章