物化視圖介紹

物化視圖是一種特殊的物理表，“物化”(Materialized)視圖是相對(duì)普通視圖而言的。

普通視圖是虛擬表，應(yīng)用的局限性大，任何對(duì)視圖的查詢，Oracle都實(shí)際上轉(zhuǎn)換為視圖SQL語句的查詢。

這樣對(duì)整體查詢性能的提高，并沒有實(shí)質(zhì)上的好處。

刷新的方法有四種：FAST、COMPLETE、FORCE和NEVER

FAST刷新采用增量刷新，只刷新自上次刷新以后進(jìn)行的修改。

COMPLETE刷新對(duì)整個(gè)物化視圖進(jìn)行完全的刷新。

FORCE方式，則Oracle在刷新時(shí)會(huì)去判斷是否可以進(jìn)行快速刷新，如果可以則采用FAST方式，否則采用COMPLETE的方式。

NEVER指物化視圖不進(jìn)行任何刷新。

物化視圖的類型：ON DEMAND、ON COMMIT

二者的區(qū)別在于刷新方法的不同：

ON DEMAND：僅在該物化視圖“需要”被刷新了，才進(jìn)行刷新(REFRESH)，即更新物化視圖，以保證和基表數(shù)據(jù)的一致性；

/*默認(rèn)情況下，如果沒指定刷新方法和刷新模式，則Oracle默認(rèn)為FORCE和DEMAND。*/
create materialized view mv_tb as select * from tb_name;

/*指定物化視圖每天刷新一次*/
create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1; 
/*要指定刷新時(shí)間比如每天晚上22：00定時(shí)刷新一次*/
create materialized view mv_name 
refresh force on demand 
start with sysdate 
next to_date( concat(to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');

ON COMMIT：一旦基表有了COMMIT，即事務(wù)提交，則立刻刷新，立刻更新物化視圖，使得數(shù)據(jù)和基表一致。

/*創(chuàng)建ON COMMIT物化視圖*/
create materialized view mv_tb refresh force on commit as select * from tb_name

創(chuàng)建時(shí)生成數(shù)據(jù)分為兩種：build immediate 和 build deferred

分為兩種：build immediate 和 build deferred

• build immediate 是在創(chuàng)建物化視圖的時(shí)候就生成數(shù)據(jù) 。
• build deferred 則在創(chuàng)建時(shí)不生成數(shù)據(jù)，以后根據(jù)需要在生成數(shù)據(jù)

如果不指定，則默認(rèn)為 build immediate

案例使用

我們?nèi)绻龅叫枰獜钠渌到y(tǒng)的數(shù)據(jù)庫中取數(shù)據(jù)進(jìn)行統(tǒng)計(jì)分析的問題，假如雙方數(shù)據(jù)庫都是ORACLE11g，同步表：test_mz_fee

1、創(chuàng)建DB_LINK

/*創(chuàng)建DB_LINK*/
create public database link dblink_his connect to system using '192.168.1.73:1521/oracle';
/*刪除DB_LINK*/
drop database link dblink_his; 
/*刪除的時(shí)候報(bào)錯(cuò)：ORA-02018：database link of same name has an open connection*/
/*關(guān)閉dblink：*/
alter session close database link dblink_his;

2、創(chuàng)建Oracle物化視圖快速刷新日志

為配合增量刷新，ORACLE要求要在住表上建立物化視圖日志。

create materialized view log on test_mz_fee with primary key  including new values;  

3、創(chuàng)建Oracle物化視圖

Oracle物化視圖，從名字上面來開，它應(yīng)該是屬于視圖，但是確實(shí)物化。

其物化是針對(duì)普通視圖并沒有真正的物理存儲(chǔ)而言，其實(shí)可以簡單的把物化視圖看做一個(gè)物理表。

create materialized view mv_test_mz_fee /*創(chuàng)建物化視圖*/  
 build immediate  /*在視圖編寫好后創(chuàng)建*/  
  refresh fast with primary key  /*根據(jù)主表主鍵增量刷新（fast，增量）  */
 on demand  /*在用戶需要時(shí)，由用戶刷新 */ 
  enable query rewrite  /*可讀寫*/  
 as  
 select * from test_mz_fee@dblink_his; /*查詢語句*/

4、視圖刷新

選擇使用ORACLE自帶工具DBMS_MVIEW工具包中REFRESH方法對(duì)物化視圖進(jìn)行刷新。

該方法有兩個(gè)參數(shù)，第一個(gè)參數(shù)是需要刷新的物化視圖名稱，第二個(gè)參數(shù)是刷新方式。

我們可以寫存儲(chǔ)過來，對(duì)每個(gè)物化視圖調(diào)用一次REFRESH方法，也可以使用“，”把物化視圖連接以來，一次刷新。如下：*/

create or replace procdure p_mview_refresh as  
begin  
dbms_mview.refresh('mv_test_mz_fee','f');  
end p_mview_refresh;  
 
/*方式2*/
create or replace procdure p_mview_refresh as  
begin  
dbms_mview.refresh('mv_test_mz_fee1,mv_test_mz_fee2','ff');
end p_mview_refresh;  

注意：

• 如果需要同時(shí)刷新多個(gè)物化視圖，必須用逗號(hào)把各個(gè)物化視圖名稱連接起來，并對(duì)每個(gè)視圖都要指明刷新方式（f、增量刷新，c、完全刷新，?、強(qiáng)制刷新）。
• 當(dāng)日志和物化視圖創(chuàng)建好后，刪除日志，則需要重新創(chuàng)建物化視圖，否則無法增量刷新。
• 因?yàn)樯厦鎸懙奈锘晥D時(shí)根據(jù)主鍵進(jìn)行更新，因此，主表必須有主鍵。*/

5、日志和物化視圖要?jiǎng)h除

drop materialized view log on test_mz_fee@dblink_his;  
drop materialized view mv_test_mz_fee1;  

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論