Oracle物化視圖：創建最簡單物化視圖

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

　　Oracle最早在OLAP系統中引入了物化視圖的概念但後來很多大型OLTP系統中發現類似統計的查詢是無可避免而這些查詢操作如果很頻繁對整體數據庫性能是很致命的於是Oracle開始不斷的改進物化視圖使得其也開始合適OLTP系統從Oracle i到現在功能已經相對比較完備了

　　本文是Oracle物化視圖系列文章的第一篇有兩個主要目的來體驗一下創建ON DEMAND和ON COMMIT物化視圖的方法ON DEMAND和ON COMMIT物化視圖的區別在於其刷新方法的不同ON DEMAND顧名思義僅在該物化視圖需要被刷新了才進行刷新(REFRESH)即更新物化視圖以保證和基表數據的一致性;而ON COMMIT是說一旦基表有了COMMIT即事務提交則立刻刷新立刻更新物化視圖使得數據和基表一致

　　第一個ON DEMAND物化視圖

　　創建ON DEMAND物化視圖

　　下面創建一個最簡單的物化視圖這個物化視圖的定義很類似於普通視圖的創建語句只是多了一個materialized但就是這個單詞造成了物化視圖和普通視圖(虛擬表)的天壤之別也引申出後面很多的事情呵呵

　　本例中需要特別注意的是Oracle給物化視圖的重要定義參數的默認值處理在下面的例子中會有特別說明因為物化視圖的創建本身是很復雜和需要優化參數設置的特別是針對大型生產數據庫系統而言但Oracle允許以這種最簡單的類似於普通視圖的辦法來做所以不可避免的會涉及到默認值問題

　　像我們這樣創建物化視圖時未作指定則Oracle按ON DEMAND模式來創建

　　從下例中可以看出

　　) 物化視圖在某種意義上說就是一個物理表(而且不僅僅是一個物理表)這通過其可以被user_tables查詢出來而得到佐證;

　　) 物化視圖也是一種段(segment)所以其有自己的物理存儲屬性;

　　) 物化視圖會占用數據庫磁盤空間這點從user_segment的查詢結果可以得到佐證

　　●  創建物化視圖

　　獲取數據庫rdbms版本信息　　

　　       SQL> select * from v$version;
　　BANNER
　　
　　Oracle Database g Enterprise Edition Release   Production
　　PL/SQL Release   Production
　　CORE  Production
　　TNS for bit Windows: Version   Production
　　NLSRTL Version  – Production

　　創建物化視圖　　

　　       SQL> create materialized view mv_testcf
　　 as
　　 select * from xiaotgtestcf;
　　Materialized view created

　　分析物化視圖以獲得統計信息　

　　       SQL> analyze table xiaotgmv_testcf compute statistics;
　　Table analyzed

　　查看物化視圖的行數發現和master表(TESTCF)一樣　　

　　       SQL> select tltable_name tlnum_rows from user_tables tl where tltable_name in ( TESTCF MV_TESTCF );
　　TABLE_NAME NUM_ROWS
　　 
　　MV_TESTCF 
　　TESTCF 

　　查看物化視圖的存儲參數　　

　　       SQL> col segment_name for a
　　SQL> select sgsegment_name sgbytes sgblocks from user_segments sg where sgsegment_name = MV_TESTCF;
　　SEGMENT_NAME BYTES BLOCKS
　　  
　　MV_TESTCF  

　　●  查看物化視圖關鍵定義

　　查看物化視圖的定義設置請關注藍色字體部分

　　這表明默認情況下如果沒指定刷新方法和刷新模式則Oracle默認為FORCE和DEMAND

　　其他的集中刷新方法和刷新模式以後將分別予以介紹

　　SQL> select mv* from user_mviews mv where mvMVIEW_NAME = MV_TESTCF;

　　(為增加查詢結果的可讀性下面進行了行列的互轉)

　　       OWNERXIAOTG
　　MVIEW_NAMEMV_TESTCF
　　CONTAINER_NAMEMV_TESTCF
　　QUERY
　　QUERY_LEN
　　UPDATABLEN
　　UPDATE_LOG
　　MASTER_ROLLBACK_SEG
　　MASTER_LINK
　　REWRITE_ENABLEDN                                         
　　REWRITE_CAPABILITYGENERAL
　　REFRESH_MODEDEMAND
　　REFRESH_METHODFORCE
　　BUILD_MODEIMMEDIATE
　　FAST_REFRESHABLEDML
　　LAST_REFRESH_TYPECOMPLETE
　　LAST_REFRESH_DATE :
　　STALENESSFRESH
　　AFTER_FAST_REFRESHFRESH
　　UNKNOWN_PREBUILTN
　　UNKNOWN_PLSQL_FUNCN
　　UNKNOWN_EXTERNAL_TABLEN
　　UNKNOWN_CONSIDER_FRESHN
　　UNKNOWN_IMPORTN
　　UNKNOWN_TRUSTED_FDN
　　COMPILE_STATEVALID
　　USE_NO_INDEXN
　　STALE_SINCE
　　NUM_PCT_TABLES
　　NUM_FRESH_PCT_REGIONS
　　NUM_STALE_PCT_REGIONS

　　測試ON DEMAND物化視圖的更新特性

　　物化視圖最重要的功能和特性之一就是其數據會隨著基表(或稱主表master表本例中為TESTCF)的變化而變基表數據增了物化視圖數據會變多;基表數據刪了物化視圖數據也會變少

　　但怎麼更新?或者說物化視圖的數據怎麼隨著基表而更新?Oracle提供了兩種方式手工刷新和自動刷新像我們這種在物化視圖定義時未作任何指定那當然是默認的手工刷新了也就是說通過我們手工的執行某個Oracle提供的系統級存儲過程或包來保證物化視圖與基表數據一致性

　　這是最基本的刷新辦法了但所謂的自動刷新其實也就是Oracle會建立一個job通過這個job來調用相同的存儲過程或包加以實現這在本系列文章的第篇會將以詳細闡述

　　下面將測試INSERTUPDATE和DELETE的測試方法類似大家有興趣的話可以自己試一試

　　需要注意的是下面暫不討論如何刷新ON DEMAND物化視圖這是下一篇文章的內容下面僅僅關注ON DEMAND物化視圖的特性及其和ON COMMIT物化視圖的區別即前者不刷新(手工或自動)就不更新物化視圖而後者不刷新也會更新物化視圖——只要基表發生了COMMIT

　　●  在基表插入測試數據

　　基表數據插入後會發現物化視圖並不會隨之更新

　　檢查基表和物化視圖是否有這一行記錄

　　       SQL> col id for a;
　　SQL> col name for a;
　　SQL> select * from xiaotgtestcf t where tid =  ;
　　ID NAME
　　 
　　SQL> select * from xiaotgmv_testcf t where tid =  ;
　　ID NAME
　　 

　　插入測試數據 

　　這時發現基表有數據但物化視圖並沒有　

　　       SQL> insert into xiaotgtestcf
　　 values (  xiaotg he he);
　　 row inserted
　　SQL> commit;
　　Commit complete

　　 測試物化視圖數據是否更新

　　從下面的實驗可以看出物化視圖數據不會更新即使等上分鐘小時或者天

　　關於如何使得ON DEMAND物化視圖數據被更新參加本系列的第二篇文章哈)　　

　　       SQL> select * from xiaotgtestcf t where tid =  ;
　　ID NAME
　　 
　　 xiaotg he he
　　SQL> select * from xiaotgmv_testcf t where tid =  ;
　　ID NAME
　　 
　　SQL>

　　第一個ON COMMIT物化視圖

　　最簡單的ON COMMIT物化視圖的創建和上面創建ON DEMAND的物化視圖區別不大因為ON DEMAND是默認的所以ON COMMIT物化視圖需要再增加個參數即可

　　 創建ON COMMIT物化視圖

　　●  創建物化視圖

　　需要注意的是無法在定義時僅指定ON COMMIT還得附帶個參數才行本例中附帶refresh force關於這個參數的意思以後將加以闡述

　　創建ON COMMIT物化視圖

　　       SQL> create materialized view mv_testcf
　　 refresh force on commit
　　 as
　　 select * from xiaotgtestcf;
　　Materialized view created
　　SQL>

　　分析物化視圖和基表　　

　　       SQL> analyze table xiaotgmv_testcf compute statistics;
　　Table analyzed
　　SQL> analyze table xiaotgtestcf compute statistics;
　　Table analyzed

　　查看當前基表和物化視圖的行數 　

　　       SQL> select tltable_name tlnum_rows from user_tables tl where tltable_name in ( TESTCF MV_TESTCF );
　　TABLE_NAME NUM_ROWS
　　 
　　MV_TESTCF 
　　TESTCF 

　　●  查看物化視圖關鍵定義

　　可以從DBA_MVIEWS中看出刷新模式為COMMIT這也是它和上面ON DEMAND物化視圖的唯一區別 

　　    　SQL> select mv* from user_mviews mv where mvMVIEW_NAME = MV_TESTCF;

　　(為增加查詢結果的可讀性下面進行了行列的互轉且只顯示前三個關鍵列的)　　

　　       REFRESH_MODECOMMIT
　　REFRESH_METHODFORCE
　　BUILD_MODEIMMEDIATE

　　 測試ON COMMIT物化視圖的更新特性

　　ON COMMIT物化視圖會在基表一旦提交時就會立刻更新物化視圖本身而且一般僅在物化視圖數據也被更新後基表數據才會事實的提交

　　這意味著這種模式可能會導致延遲基表數據的提交這點在下面的實驗中體現得很清楚

　　實驗中對基表TESTCF平常的COMMIT在秒內可以完成但在有了ON COMMIT視圖MV_TESTCF後居然要秒速度減低了很多倍ON COMMIT視圖對基表的影響可見一斑

　　●  在基表中插入數據　　

　　
SQL> set timing on;
　　SQL> insert into xiaotgtestcf ( id name ) values (   xiaotg again he he );
　　 row inserted
　　Executed in  seconds
　　SQL> commit;
　　Commit complete
　　Executed in  seconds
　　SQL> select * from xiaotgtestcf where id = ;
　　ID NAME
　　 
　　 xiaotg again he he
　　Executed in  seconds
　　SQL> col id for a;
　　SQL> col name for a;
　　SQL> select * from xiaotgtestcf where id = ;
　　ID NAME
　　 
　　 xiaotg again he he
　　Executed in  seconds
　　SQL> select * from xiaotgmv_testcf where id = ;
　　ID NAME
　　  
　　 xiaotg again he he
　　Executed in  seconds

　　●  測試基表正常情況下的COMMIT速度　　

　　       SQL> drop materialized view mv_testcf;
　　Materialized view dropped
　　Executed in  seconds
　　SQL>
　　SQL>
　　SQL> insert into xiaotgtestcf ( id name ) values (   xiaotg again he he  );
　　 row inserted
　　Executed in  seconds
　　SQL> commit;
　　Commit complete
　　Executed in  seconds

From:http://tw.wingwit.com/Article/program/Oracle/201311/17144.html