物化視圖是一種特殊的物理表，“物化”(Materialized)視圖是相對普通視圖而言的。普通視圖是虛擬表，應用的局限性大，任何對視圖的查詢(xún)，Oracle都實(shí)際上轉換為視圖SQL語(yǔ)句的查詢(xún)。這樣對整體查詢(xún)性能的提高，并沒(méi)有實(shí)質(zhì)上的好處。
Oracle最早在OLAP系統中引入了物化視圖的概念。但后來(lái)很多大型OLTP系統中，發(fā)現類(lèi)似統計的查詢(xún)是無(wú)可避免，而這些查詢(xún)操作如果很頻繁，對整體數據庫性能是很致命的。于是Oracle開(kāi)始不斷的改進(jìn)物化視圖，使得其也開(kāi)始合適OLTP系統。從Oracle 8i到現在，功能已經(jīng)相對比較完備了。
本文是Oracle物化視圖系列文章的第一篇，有兩個(gè)主要目的，來(lái)體驗一下創(chuàng )建ON DEMAND和ON COMMIT物化視圖的方法。ON DEMAND和ON COMMIT物化視圖的區別在于其刷新方法的不同，ON DEMAND顧名思義，僅在該物化視圖“需要”被刷新了，才進(jìn)行刷新(REFRESH)，即更新物化視圖，以保證和基表數據的一致性;而ON COMMIT是說(shuō)，一旦基表有了COMMIT，即事務(wù)提交，則立刻刷新，立刻更新物化視圖，使得數據和基表一致。
1、第一個(gè)ON DEMAND物化視圖
1.1、創(chuàng )建ON DEMAND物化視圖
下面創(chuàng )建一個(gè)最簡(jiǎn)單的物化視圖，這個(gè)物化視圖的定義很類(lèi)似于普通視圖的創(chuàng )建語(yǔ)句，只是多了一個(gè)materialized，但就是這個(gè)單詞，造成了物化視圖和普通視圖(虛擬表)的天壤之別，也引申出后面很多的事情，呵呵。
本例中需要特別注意的是，Oracle給物化視圖的重要定義參數的默認值處理，在下面的例子中會(huì )有特別說(shuō)明。因為物化視圖的創(chuàng )建本身是很復雜和需要優(yōu)化參數設置的，特別是針對大型生產(chǎn)數據庫系統而言。但Oracle允許以這種最簡(jiǎn)單的，類(lèi)似于普通視圖的辦法來(lái)做，所以不可避免的會(huì )涉及到默認值問(wèn)題。
像我們這樣，創(chuàng )建物化視圖時(shí)未作指定，則Oracle按ON DEMAND模式來(lái)創(chuàng )建。
從下例中可以看出：
1) 物化視圖在某種意義上說(shuō)就是一個(gè)物理表(而且不僅僅是一個(gè)物理表)，這通過(guò)其可以被user_tables查詢(xún)出來(lái)，而得到佐證;
2) 物化視圖也是一種段(segment)，所以其有自己的物理存儲屬性;
3) 物化視圖會(huì )占用數據庫磁盤(pán)空間，這點(diǎn)從user_segment的查詢(xún)結果，可以得到佐證。
● 創(chuàng )建物化視圖
--獲取數據庫rdbms版本信息
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 – Production
--創(chuàng )建物化視圖
SQL> create materialized view mv_testcf
2 as
3 select * from xiaotg.testcf;
Materialized view created
--分析物化視圖，以獲得統計信息
SQL> analyze table xiaotg.mv_testcf compute statistics;
Table analyzed
--查看物化視圖的行數，發(fā)現和master表(TESTCF)一樣
SQL> select tl.table_name, tl.num_rows from user_tables tl where tl.table_name in ( 'TESTCF', 'MV_TESTCF' );
TABLE_NAME NUM_ROWS
------------------------------ ----------
MV_TESTCF 80000
TESTCF 80000
--查看物化視圖的存儲參數
SQL> col segment_name for a24
SQL> select sg.segment_name, sg.bytes, sg.blocks from user_segments sg where sg.segment_name = 'MV_TESTCF';
SEGMENT_NAME BYTES BLOCKS
------------------------ ---------- ----------
MV_TESTCF 9437184 1152
● 查看物化視圖關(guān)鍵定義
--查看物化視圖的定義設置，請關(guān)注藍色字體部分。
--這表明，默認情況下，如果沒(méi)指定刷新方法和刷新模式，則Oracle默認為FORCE和DEMAND。
--其他的集中刷新方法和刷新模式以后將分別予以介紹。
SQL> select mv.* from user_mviews mv where mv.MVIEW_NAME = 'MV_TESTCF';
(為增加查詢(xún)結果的可讀性，下面進(jìn)行了行列的互轉)
OWNERXIAOTG
MVIEW_NAMEMV_TESTCF
CONTAINER_NAMEMV_TESTCF
QUERY
QUERY_LEN80
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_DATE2008-9-9 15:02
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_TABLES0
NUM_FRESH_PCT_REGIONS
NUM_STALE_PCT_REGIONS
1.2、測試ON DEMAND物化視圖的更新特性
物化視圖最重要的功能和特性之一，就是其數據會(huì )隨著(zhù)基表(或稱(chēng)主表，master表，本例中為T(mén)ESTCF)的變化而變，基表數據增了，物化視圖數據會(huì )變多;基表數據刪了，物化視圖數據也會(huì )變少。
但怎么更新?或者說(shuō)物化視圖的數據怎么隨著(zhù)基表而更新?Oracle提供了兩種方式，手工刷新和自動(dòng)刷新，像我們這種，在物化視圖定義時(shí)，未作任何指定，那當然是默認的手工刷新了。也就是說(shuō)，通過(guò)我們手工的執行某個(gè)Oracle提供的系統級存儲過(guò)程或包，來(lái)保證物化視圖與基表數據一致性。
這是最基本的刷新辦法了。但所謂的自動(dòng)刷新，其實(shí)也就是Oracle會(huì )建立一個(gè)job，通過(guò)這個(gè)job來(lái)調用相同的存儲過(guò)程或包，加以實(shí)現，這在本系列文章的第2篇會(huì )將以詳細闡述。
下面將測試INSERT，UPDATE和DELETE的測試方法類(lèi)似，大家有興趣的話(huà)，可以自己試一試。
需要注意的是，下面暫不討論如何刷新ON DEMAND物化視圖，這是下一篇文章的內容。下面僅僅關(guān)注ON DEMAND物化視圖的特性及其和ON COMMIT物化視圖的區別，即前者不刷新(手工或自動(dòng))就不更新物化視圖，而后者不刷新也會(huì )更新物化視圖，——只要基表發(fā)生了COMMIT。
● 在基表插入測試數據
基表數據插入后，會(huì )發(fā)現，物化視圖并不會(huì )隨之更新。
--檢查基表和物化視圖是否有80001這一行記錄。
SQL> col id for a10;
SQL> col name for a30;
SQL> select * from xiaotg.testcf t where t.id = 80001 ;
ID NAME
---------- ------------------------------
SQL> select * from xiaotg.mv_testcf t where t.id = 80001 ;
ID NAME
---------- ------------------------------
--插入測試數據80001
--這時(shí)發(fā)現，基表有數據，但物化視圖并沒(méi)有
SQL> insert into xiaotg.testcf
2 values ( 80001, 'xiaotg he he');
1 row inserted
SQL> commit;
Commit complete
1.2.2 測試物化視圖數據是否更新
從下面的實(shí)驗可以看出，物化視圖數據不會(huì )更新，即使等上1分鐘、1小時(shí)、或者1天。
關(guān)于如何使得ON DEMAND物化視圖數據被更新，參加本系列的第二篇文章哈：)
SQL> select * from xiaotg.testcf t where t.id = 80001 ;
ID NAME
---------- ------------------------------
80001 xiaotg he he
SQL> select * from xiaotg.mv_testcf t where t.id = 80001 ;
ID NAME
---------- ------------------------------
SQL>
2、第一個(gè)ON COMMIT物化視圖
最簡(jiǎn)單的ON COMMIT物化視圖的創(chuàng )建，和上面創(chuàng )建ON DEMAND的物化視圖區別不大。因為ON DEMAND是默認的，所以ON COMMIT物化視圖，需要再增加個(gè)參數即可。
2.1 創(chuàng )建ON COMMIT物化視圖
● 創(chuàng )建物化視圖
需要注意的是，無(wú)法在定義時(shí)僅指定ON COMMIT，還得附帶個(gè)參數才行，本例中附帶refresh force，關(guān)于這個(gè)參數的意思，以后將加以闡述。
--創(chuàng )建ON COMMIT物化視圖
SQL> create materialized view mv_testcf2
2 refresh force on commit
3 as
4 select * from xiaotg.testcf;
Materialized view created
SQL>
--分析物化視圖和基表
SQL> analyze table xiaotg.mv_testcf2 compute statistics;
Table analyzed
SQL> analyze table xiaotg.testcf compute statistics;
Table analyzed
--查看當前基表和物化視圖的行數
SQL> select tl.table_name, tl.num_rows from user_tables tl where tl.table_name in ( 'TESTCF', 'MV_TESTCF2' );
TABLE_NAME NUM_ROWS
------------------------------ ----------
MV_TESTCF2 80000
TESTCF 80000
● 查看物化視圖關(guān)鍵定義
可以從DBA_MVIEWS中看出，刷新模式為COMMIT，這也是它和上面ON DEMAND物化視圖的唯一區別。
SQL> select mv.* from user_mviews mv where mv.MVIEW_NAME = 'MV_TESTCF';
(為增加查詢(xún)結果的可讀性，下面進(jìn)行了行列的互轉，且只顯示前三個(gè)關(guān)鍵列的)
REFRESH_MODECOMMIT
REFRESH_METHODFORCE
BUILD_MODEIMMEDIATE
2.2 測試ON COMMIT物化視圖的更新特性
ON COMMIT物化視圖會(huì )在基表一旦提交時(shí)，就會(huì )立刻更新物化視圖本身，而且一般僅在物化視圖數據也被更新后，基表數據才會(huì )事實(shí)的提交。
這意味著(zhù)，這種模式可能會(huì )導致延遲基表數據的提交。。這點(diǎn)在下面的實(shí)驗中體現得很清楚。
實(shí)驗中，對基表TESTCF，平常的COMMIT在0.01秒內可以完成，但在有了ON COMMIT視圖MV_TESTCF2后，居然要6秒。速度減低了很多倍。ON COMMIT視圖對基表的影響可見(jiàn)一斑。
● 在基表中插入數據
SQL> set timing on;
SQL> insert into xiaotg.testcf ( id, name ) values ( 80002, ' xiaotg again he he ');
1 row inserted
Executed in 0.015 seconds
SQL> commit;
Commit complete
Executed in 6.985 seconds
SQL> select * from xiaotg.testcf where id = 80002;
ID NAME
--------------------------------- --------------------------------------------------------------------------------
80002 xiaotg again he he
Executed in 0 seconds
SQL> col id for a10;
SQL> col name for a40;
SQL> select * from xiaotg.testcf where id = 80002;
ID NAME
---------- ----------------------------------------
80002 xiaotg again he he
Executed in 0.016 seconds
SQL> select * from xiaotg.mv_testcf2 where id = 80002;
ID NAME
---------- ----------------------------------------
80002 xiaotg again he he
Executed in 0.031 seconds
● 測試基表正常情況下的COMMIT速度
SQL> drop materialized view mv_testcf2;
Materialized view dropped
Executed in 1.984 seconds
SQL>
SQL>
SQL> insert into xiaotg.testcf ( id, name ) values ( 80003, ' xiaotg again he he 3 ');
1 row inserted
Executed in 0 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
聯(lián)系客服
