Oracle物化檢視詳解

Rangle發表於2018-02-28
Oracle

現實工作中會有多個資料來源同步到一個資料庫完成資料分析的場景，這些資料可以不是實時同步的，我們一般通過定時任務抽取資料到統計分析庫給應用使用。

一般的同步方式可以通過時間戳做全量和增量資料同步（存在原資料變化可能，資料不一致的情況），也可以通過dblink做資料實時查詢（較損耗線上資料庫效能），一般最好的方式是通過建立物化檢視，然後通過schedual job完成定時資料同步，這裡就記錄下物化檢視的使用。

一、物化檢視簡介

物化檢視是一種特殊的物理表，“物化”(Materialized)檢視是相對普通檢視而言的。普通檢視是虛擬表，應用的侷限性大，任何對檢視的查詢，Oracle都實際上轉換為檢視SQL語句的查詢。這樣對整體查詢效能的提高，並沒有實質上的好處。

1、物化檢視分類

ON DEMAND：該物化檢視“需要”被重新整理了，才進行重新整理(REFRESH)，即更新物化檢視，以保證和基表資料的一致性；

ON COMMIT：一旦基表有了COMMIT，即事務提交，則立刻重新整理，立刻更新物化檢視，使得資料和基表一致；

預設情況建立物化檢視不指定型別，則是按需重新整理（on demand）

2、物化檢視

 

二、物化檢視使用

1、物化檢視建立

物化檢視的資料來源於基表，而重新整理的起始點記錄於物化檢視日誌，所以建立物化檢視授權必須有基表——>物化檢視日誌（基於基表）——>物化檢視

物化檢視建立示例：

在dbtest下建立物化檢視T，其中基表是scott使用者下的dept表
（1）授權dbtest使用者可以查詢scott.dept
grant select on scott.dept to dbtest;
（2）在dbtest使用者下建立表T （若建立物化檢視加on prebuilt table） 
create table t as select * from scott.dept where 1=2; 
（3）在scott使用者下建立物化檢視日誌，在dbtest下建立物化檢視T 
建立物化檢視日誌： 
conn scott/tiger; 
create materialized view log on dept; 
grant select on MLOG$_DEPT to dbtest;
建立物化檢視： 
conn dbtest/dbtest; 
create materialized view T
on prebuilt table
refresh fast on demand
as
select deptno,dname,loc,ACOLUMN from scott.dept;

##可以通過在 view T後加上BUILD IMMEDIATE引數立刻重新整理物化檢視，得到資料

REFRESH 子句可以包含如下部分：
   [refresh [fast|complete|force]
   [on demand | commit]
   [start with date] [next date]
   [with {primary key|rowid}]]

2、物化檢視重新整理

當基表有更新後（DML），如果不是on commit型別，物化檢視需要重新整理後資料才能保持和基表一致，重新整理方式有全量重新整理（COMPLETE）、快速重新整理（增量FAST）、強制重新整理（FORCE）、不重新整理（NEVER）

FAST：增量快速重新整理 

exec dbms_mview.refresh('表名', 'F')  

exec dbms_mview.refresh('dbtest.t','F');

COMPLETE：全量重新整理

exec dbms_mview.refresh('表名', 'C')   ;

exec dbms_mview.refresh('dbtest.t','C');

FORCE：重新整理時判斷否可以快速重新整理，如果能快速重新整理則執行fast重新整理，如果不能則執行complete重新整理

NEVER：不重新整理

3、物化檢視刪除

drop MATERIALIZED VIEW  mview_name;

4、物化檢視日誌刪除

物化檢視日誌是mlog$_basetablename命名格式

DROP MATERIALIZED VIEW LOG  on base_table_name;
MLOG$_DEPT 

 DEPTNO           主鍵列
 SNAPTIME$$       用於表示重新整理時間
 DMLTYPE$$        用於表示dml操作型別，i表示insert，d表示delete，u表示update
 OLD_NEW$$        用於表示這個值是新值還是舊值。n（ew）表示新值（一般為delete操作），o（ld）表示舊值（一般為Insert操作），u表示update操作。
 CHANGE_VECTOR$$  表示修改向量，用來表示被修改的是哪個或哪幾個欄位
 XID$$              

如果with後面跟了primary key，則物化檢視日誌中會包含主鍵列。
如果with後面跟了rowid，則物化檢視日誌中會包含： m_row$$：用來儲存發生變化的記錄的rowid。
如果with後面跟了object id，則物化檢視日誌中會包含：sys_nc_oid$：用來記錄每個變化物件的物件id。
如果with後面跟了sequence，則物化檢視日子中會包含：sequence$$：給每個操作一個sequence號，從而保證重新整理時按照順序進行重新整理。
如果with後面跟了一個或多個column名稱，則物化檢視日誌中會包含這些列。  

當基本表發生dml操作時，會記錄到物化檢視日誌中，這時指定的時間4000年1月1日0時0分0秒（物化檢視未被重新整理）。
如果物化檢視日誌供多個物化檢視使用，則一個物化檢視重新整理後會將它重新整理的記錄的時間更新為它重新整理的時間。
只有建立快速重新整理的物化檢視才能使用物化檢視日誌，如果只建立一個物化檢視，則物化檢視重新整理完會將物化檢視日誌清除掉

--當建立物化檢視日誌使用primary key時，oracle建立臨時表 RUPD$_基礎表

5、檢視物化檢視

set line 200;
set pagesize 20000;
col owner for a15;
col mview_name for a30;
col query for a60;

select owner,mview_name,refresh_method,last_refresh_date,compile_state from dba_mviews;

#如果要看具體語句，可以通過query欄位檢視

 

三、附錄

 

物化檢視是一種特殊的物理表，“物化”(Materialized)檢視是相對普通檢視而言的。普通檢視是虛擬表，應用的侷限性大，任何對檢視的查詢，Oracle都實際上轉換為檢視SQL語句的查詢。這樣對整體查詢效能的提高，並沒有實質上的好處。

 

相關文章