【MV】group by查詢子句是否包含count(*)對物化檢視重新整理的影響
建立快速可更新物化檢視的查詢語句，使用group by進行分類查詢時，是否使用count(*)將對物化檢視重新整理產生影響。本文給出測試過程，並進行總結。
1.初始化環境
1）準備物化檢視基表
create table t (x int, y int,z int);
insert into t values (1,1,1);
insert into t values (2,2,2);
insert into t values (3,3,3);
insert into t values (4,4,4);
insert into t values (5,5,5);
insert into t values (6,6,6);
insert into t values (6,6,6);
commit;
sec@ora10g> select * from t;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 rows selected.
2）建立物化檢視日誌
注：包含所有欄位
sec@ora10g> create materialized view log on t with sequence, rowid (x,y,z) including new values;
Materialized view log created.
3）建立物化檢視
sec@ora10g> create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z from t group by x,y,z;
Materialized view created.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 rows selected.
注意：此處建立的物化檢視中未包含“count(*)”！
2.測試物化檢視使用效果
1）驗證建立結果
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 rows selected.
2）驗證物化檢視是否隨記錄增加而更新
sec@ora10g> insert into t values (7,7,7);
1 row created.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 7 7
8 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 rows selected.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 7 7
8 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
結論一：針對插入操作，資料變化可以隨commit重新整理到物化檢視中，不過注意這是有前提的，前提是插入操作之前沒有刪除和更新操作。
3）驗證物化檢視是否隨記錄刪除而更新
sec@ora10g> delete from t where x=1;
1 row deleted.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 7 7
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 7 7
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
結論二：針對刪除操作，資料變化無法隨commit重新整理到物化檢視。
4）驗證物化檢視是否隨記錄修改而更新
sec@ora10g> update t set x=8,y=8,z=8 where x=7;
1 row updated.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
8 8 8
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
8 8 8
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
結論三：針跟新除操作，資料變化無法隨commit重新整理到物化檢視。
3.可以手動完全重新整理解決此問題
sec@ora10g> exec dbms_mview.refresh('mv_t', 'c');
PL/SQL procedure successfully completed.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
8 8 8
6 rows selected.
此時物化檢視內容已經根據基表內容重新整理完畢，結果正確。
4.完美解決方案
保證隨基表的變化重新整理物化檢視的根本解決方法是，在建立物化檢視的查詢語句中使用count(*)。
create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;
具體測試過程這裡不贅述。
5.小結
根據這裡測試結果提醒我們需要注意的內容是，瞭解物化檢視不同建立方法對重新整理結果的影響。防止不必要的問題發生。
Good luck.
secooler
11.06.13
-- The End --
1.初始化環境
1）準備物化檢視基表
create table t (x int, y int,z int);
insert into t values (1,1,1);
insert into t values (2,2,2);
insert into t values (3,3,3);
insert into t values (4,4,4);
insert into t values (5,5,5);
insert into t values (6,6,6);
insert into t values (6,6,6);
commit;
sec@ora10g> select * from t;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 rows selected.
2）建立物化檢視日誌
注：包含所有欄位
sec@ora10g> create materialized view log on t with sequence, rowid (x,y,z) including new values;
Materialized view log created.
3）建立物化檢視
sec@ora10g> create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z from t group by x,y,z;
Materialized view created.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 rows selected.
注意：此處建立的物化檢視中未包含“count(*)”！
2.測試物化檢視使用效果
1）驗證建立結果
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 rows selected.
2）驗證物化檢視是否隨記錄增加而更新
sec@ora10g> insert into t values (7,7,7);
1 row created.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 7 7
8 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 rows selected.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 7 7
8 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
結論一：針對插入操作，資料變化可以隨commit重新整理到物化檢視中，不過注意這是有前提的，前提是插入操作之前沒有刪除和更新操作。
3）驗證物化檢視是否隨記錄刪除而更新
sec@ora10g> delete from t where x=1;
1 row deleted.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 7 7
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
7 7 7
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
結論二：針對刪除操作，資料變化無法隨commit重新整理到物化檢視。
4）驗證物化檢視是否隨記錄修改而更新
sec@ora10g> update t set x=8,y=8,z=8 where x=7;
1 row updated.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
8 8 8
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select * from t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
6 6 6
8 8 8
7 rows selected.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
7 rows selected.
結論三：針跟新除操作，資料變化無法隨commit重新整理到物化檢視。
3.可以手動完全重新整理解決此問題
sec@ora10g> exec dbms_mview.refresh('mv_t', 'c');
PL/SQL procedure successfully completed.
sec@ora10g> select * from mv_t order by x;
X Y Z
---------- ---------- ----------
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
8 8 8
6 rows selected.
此時物化檢視內容已經根據基表內容重新整理完畢，結果正確。
4.完美解決方案
保證隨基表的變化重新整理物化檢視的根本解決方法是，在建立物化檢視的查詢語句中使用count(*)。
create materialized view mv_t build immediate refresh fast on commit enable query rewrite as select x,y,z,count(*) from t group by x,y,z;
具體測試過程這裡不贅述。
5.小結
根據這裡測試結果提醒我們需要注意的內容是，瞭解物化檢視不同建立方法對重新整理結果的影響。防止不必要的問題發生。
Good luck.
secooler
11.06.13
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-697897/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【MV】物化檢視查詢重寫
- 包含複雜查詢的快速重新整理的物化檢視
- Oracle 物化檢視快速重新整理對效能的影響Oracle
- 隱式轉換影響物化檢視查詢重寫
- MV (Materialed View) 物化檢視的重新整理組View
- 物化檢視日誌對UPDATE的影響
- Oracle物化檢視3 - Prebuilt MVOracleUI
- 建立物化檢視MV ( Materialized View )ZedView
- 使用物化檢視查詢重寫 優化對於 UNION ALL檢視的CONNECT BY查詢優化
- 淺析為何Oracle物化檢視對distinct, group by不支援快速重新整理Oracle
- 【物化檢視】根據物化檢視日誌快速重新整理物化檢視的過程
- 物化檢視的快速重新整理測試與物化檢視日誌
- 12c 物化檢視 - 對快速重新整理的理解
- 【MV】實現跨庫可更新物化檢視
- 12c 物化檢視 - 理解完全重新整理的物化檢視工作原理
- 【SQL】Oracle查詢轉換之物化檢視查詢重寫SQLOracle
- oracle 物化檢視重新整理方法Oracle
- Oracle如何根據物化檢視日誌快速重新整理物化檢視Oracle
- DB2資料庫物化檢視：MQT物化查詢表的使用DB2資料庫MQQT
- MySQL：查詢欄位數量多少對查詢效率的影響MySql
- 物化檢視重新整理並非完全根據物化檢視日誌記錄
- oracle 物化檢視的自動重新整理方法Oracle
- 物化檢視重新整理的問題及分析
- Arraysize的設定以及對查詢效能的影響
- 10G物化檢視PCT快速重新整理不再需要物化檢視日誌（三）
- 10G物化檢視PCT快速重新整理不再需要物化檢視日誌（二）
- 10G物化檢視PCT快速重新整理不再需要物化檢視日誌（一）
- 物化檢視日誌與增量重新整理
- 淺析物化檢視與查詢重寫(Enable query rewrite)
- 檢視insert,delete,update對基表的影響（檢視初識）delete
- 並行查詢對於響應時間的影響實驗並行
- 【ORACLE】物化檢視快速重新整理限制條件Oracle
- Oracle 物化檢視 快速重新整理 限制 說明Oracle
- dex最佳化對Arouter查詢路徑的影響
- Postgresql MVCC架構對從庫長查詢的影響SQLMVC架構
- SQL查詢結果集對注入的影響及利用SQL
- 物化檢視(Materialized View)的重新整理回滾約束ZedView
- 物化檢視(Materialized View)的重新整理回滾測試ZedView