12c 物化檢視 - 對快速重新整理的理解

shilei1發表於2018-01-23
前一篇部落格用一個簡單的示例描述了完全重新整理的物化檢視。完全重新整理的物化檢視每次重新整理都需要對儲存資料的基表執行delete操作，在將新的結果集insert到基表。為了減少這個開銷，為了減少這個開銷Oracle為我們提供了快速重新整理物化檢視。

實驗之前先明確兩個定義：
主表：SH.costs ，example中的示例表
基表：SH.mv_costs ，儲存查詢結果集的實體表，也就是“模擬物化檢視”的真正儲存資料的地方。
增量基表：記錄主表資料變化的表

什麼是快速重新整理？
快速重新整理就是增量重新整理，只應用主表產生的insert、update、delete操作。就像是RMAN中的增量備份，只備0級增量備份後的變化。

快速重新整理的實現需要在主表上建立一個日誌檔案，該檔案記錄了主表被修改的記錄。其實該檔案也是一張基表，儲存了主表的增量變化。增量基表記錄變化時依賴於主表的主鍵或者是rowid。當觸發快速重新整理後，增量基表中的變化資料會更新到物化檢視的基表中，然後將自己清空等待主表的變化。從另一個角度來看增量基表採用了快取的原理，將資料變化收集起來，分批次載入到物化檢視基表，減少完全重新整理的消耗。

下面透過一個實驗來看看快速重新整理的過程

[oracle@snow ~]$ sqlplus / as sysdba

SYS@cdb > conn sh/sh@pdb1

目前COSTS表中沒有主鍵，在建立MV日誌時只能使用ROWID
SH@pdb1 > col owner for a20
SH@pdb1 > col constraint_name for a20
SH@pdb1 > select owner,constraint_name,constraint_type from dba_constraints where table_name='COSTS';

OWNER CONSTRAINT_NAME C
-------------------- -------------------- -
SH SYS_C009967 C
SH SYS_C009966 C
SH SYS_C009965 C
SH SYS_C009964 C
SH SYS_C009963 C
SH SYS_C009962 C
SH COSTS_CHANNEL_FK R
SH COSTS_TIME_FK R
SH COSTS_PRODUCT_FK R
SH COSTS_PROMO_FK R

1. 採用rowid的方式建立物化檢視日誌
SH@pdb1 > create materialized view log on costs with rowid;

Materialized view log created.

如果有主鍵的話可以使用
SH@pdb1 > create materialized view log on costs with primary key;

2. 建立快速重新整理的物化檢視

SH@pdb1 > create materialized view mvf_costs
2 segment creation immediate
3 refresh with rowid fast on demand
4 as select promo_id,sum(unit_cost) cost from costs group by promo_id;
as select promo_id,sum(unit_cost) cost from costs group by promo_id
*
ERROR at line 4:
ORA-32401: materialized view log on "SH"."COSTS" does not have new values

錯誤提示剛剛建立的物化檢視日誌不符合要求。將其刪除，調整後重新建立。
SH@pdb1 > drop materialized view log on costs;

Materialized view log dropped.

重新建立物化檢視日誌，並且新增Including new values字句。
其中sequence是透過promo_id和unit_cost記錄DML操作的次序，當重新整理時按照這個次序在基表上執行

SH@pdb1 >
create materialized view log on costs with rowid,
sequence(promo_id,unit_cost) including new values;

Materialized view log created.

讓我們看看物化檢視日誌建立成功後會包含兩個物件，其中MLOG$_COSTS是增量基表。當主表產生資料變化時，隱含的trigger會將其寫入該表中。
SH@pdb1 > col object_name for a30
SH@pdb1 > select object_name,object_type from user_objects where object_name like '%_COSTS%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
I_MLOG$_COSTS                  INDEX
MLOG$_COSTS                    TABLE

修改後的物化檢視日誌滿足要求，再次建立物化檢視成功。
SH@pdb1 >
create materialized view mvf_costs
segment creation immediate
refresh fast with rowid on demand
as select promo_id,sum(unit_cost) cost from costs group by promo_id;

下面看看物化檢視mvf_costs的家族成員吧。MVF_COSTS（MATERIALIZED VIEW）是資料的邏輯容器；MVF_COSTS（TABLE）是儲存查詢結果的基表；MLOG$_COSTS是增量基表儲存主表COSTS的資料變化；I_SNAP$_MVF_COSTS是MV的唯一索引，它是Oracle為了提高重新整理效能二自動建立的；I_MLOG$_COSTS是增量基表的索引。

SH@pdb1 > col object_name for a30
SH@pdb1 > select object_name,object_type from user_objects where object_name like '%_COSTS%';

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
I_MLOG$_COSTS                  INDEX
I_SNAP$_MVF_COSTS              INDEX
MLOG$_COSTS                    TABLE
MVF_COSTS                      TABLE
MVF_COSTS                      MATERIALIZED VIEW

下面開始測試一下重新整理功能

向主表costs新增1000條資料
SH@pdb1 > insert into costs select * from costs where rownum < 1001;
1000 rows created.
SH@pdb1 > commit;
Commit complete.

檢查增量基表會看到新增了1000條記錄
SH@pdb1 > select count(*) from MLOG$_COSTS;

COUNT(*)
----------
1000

手工執行一次快速重新整理

SH@pdb1 > exec dbms_mview.refresh('MVF_COSTS','F');

PL/SQL procedure successfully completed.


快速重新整理執行完畢，增量基表中的資料不再有用。
SH@pdb1 > select count(*) from MLOG$_COSTS;

COUNT(*)
----------
0

下面是重新整理前後物化檢視的資料變化
SH@pdb1 > select * from mvf_costs;

PROMO_ID   COST
---------- ----------
999        9173508.88
350        242949.08
351        258268

SH@pdb1 > select * from mvf_costs;

PROMO_ID   COST
---------- ----------
999        9313900.88
350        242949.08
351        258268



上述實驗對於剛剛接觸物化檢視的同學來說非常容易理解。我也從中收益過，特此分享一下。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-2150452/，如需轉載，請註明出處，否則將追究法律責任。

相關文章