Oracle普通視圖和實體化視圖比較
來源：茂盛博客
|
2013-07-30
Oracle普通視圖和實體化視圖比較
相對于普通的視圖來說，實體化視圖的不同之處在于實體化視圖管理存儲數據，占據
數據庫
的物理空間。實體化視圖的結果會保存在一個普通的數據表中，在對實體化視圖進行查詢的時候不再會對創建實體化視圖的基表進行查詢，而是直接查詢實體化視圖對應的結果表，然后通過定期的刷新機制來更新MView表中的數據。?
首先我們需要創建表，然后寫一個 SELECT 語句 。?
SQL> create table xjzhang_table1 (a varchar2(10),b number(10));?
表已創建。?
SQL> create table xjzhang_table2 (a varchar2(10),b number(10));?
表已創建。?
向兩張表中插入數據?
SQL> insert into xjzhang_table1 values ('aaa','00001');?
已創建 1 行。?
SQL> insert into xjzhang_table1 values ('bbb','00002');?
已創建 1 行。?
SQL> insert into xjzhang_table2 values ('aa1','00002');?
已創建 1 行。?
SQL> insert into xjzhang_table2 values ('bb1','00003');?
已創建 1 行。?
SQL> commit;?
提交完成。?
然后我們創建一個視圖，視圖的名稱為xjzhang_view?
SQL> create view xjzhang_view as select xjzhang_table1.a,xjzhang_table2.b from xjzhang_table1,xjzhang_table2 where xjzhang_table1.b=xjzhang_table2.b;?
視圖已創建。?
然后我們查詢視圖?
SQL> select * from xjzhang_view;?
A ? ? ? ? ?B?
---------- ----------?
bbb ? ? ? ?2?
然后我們寫一個 查詢語句?
SQL> select xjzhang_table1.a,xjzhang_table2.b from xjzhang_table1,xjzhang_table2 where xjzhang_table1.b=xjzhang_table2.b;?
A ? ? ? ? ?B?
---------- ----------?
bbb ? ? ? ?2?
可以看到我們查詢視圖的結果和查詢那個SELECT語句的結果是一致的，說明視圖是查詢一個 或者多個表的 SELECT 語句的描述。?
查詢一下我們創建的視圖?
select object_name,object_type,created,status from dba_objects where?
object_name='XJZHANG_VIEW';?
OBJECT_NAME ? ? OBJECT_TYPE ? ? ? ? CREATED ? ? ? ?STATUS?
--------------- ------------------- -------------- ------- XJZHANG_VIEW ? ?VIEW ? ? ? ? ? ? ? ?24-6月 -09 ? ? VALID?
同樣我們先創建 一張表表名為 xjzhang_table3同時對表插入數據?
SQL> create table xjzhang_table3 (a varchar2(10),b number(5));?
表已創建。?
SQL> insert into xjzhang_table3 values ('aaa','00001');?
已創建 1 行。?
SQL> insert into xjzhang_table3 values ('bbb','00002');?
已創建 1 行。?
SQL> commit;?
提交完成。?
下面我們開始創建實體視圖 （這里 我們創建的實體視圖 不是自動刷新 而是需要手動去刷新）?
SQL> create materialized view xjzhang_mat_view as select * from xjzhang_table3;?
實體化視圖已創建。?
我們查詢一下我們創建的實體視圖，實體視圖的名稱為 xjzhang_mat_view?
SQL> select * from xjzhang_mat_view;?
A ? ? ? ? ?B?
---------- ----------?
aaa ? ? ? ?1?
bbb ? ? ? ?2?
實體視圖從某種意義上說是一張物理表可以通過 DBA_TABLES 進行查詢來論證一下?
SQL>select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='XJZHANG_MAT_VIEW';?
TABLE_NAME ? ? ? ? ? ? ? ? ? ? TABLESPACE_NAME ? ? ? ? ? ? ? ?STATUS?
------------------------------ ------------------------------ --------?
XJZHANG_MAT_VIEW ? ? ? ? ? ? ? SYSTEM ? ? ? ? ? ? ? ? ? ? ? ? VALID?
我們來查詢一下剛才創建的 視圖 xjzhang_view?
SQL> select TABLE_NAME,TABLESPACE_NAME,STATUS from dba_tables where table_name='XJZHANG_VIEW';?
未選定行?
可以看出普通視圖在DBA_TABLES 中 是沒有記錄的，也沒有對應的表空間?
實體視圖會占用一定的存儲空間，因為它存放了查詢的結果集，那么它也是一種段，可以在DBA_SEGMENTS 中查詢出?
SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name='XJZHANG_MAT_VIEW';?
SEGMENT_NAME ? ? ? ? SEGMENT_TYPE ? ? ? TABLESPACE_NAME?
-------------------- ------------------ ------------------------------XJZHANG_MAT_VIEW ? ? TABLE ? ? ? ? ? ? ?SYSTEM?
同樣我們通過 DBA_SEGMENTS 來查詢一下我們創建的普通視圖?
SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name='XJZHANG_VIEW';?
未選定行?
可以看出普通視圖是不被記錄在 DBA_SEGMENTS 中的。?
下面我們更新一下XJZHANG_TABLE3 表中的信息，看一下 實體視圖的變化信息?
SQL> insert into xjzhang_table3 values ('ccc','00003');?
已創建 1 行。?
SQL> commit;?
提交完成。?
查詢該表的信息?
SQL> select * from xjzhang_table3;?
A ? ? ? ? ?B?
---------- ----------?
aaa ? ? ? ?1?
bbb ? ? ? ?2?
ccc ? ? ? ?3?
表的記錄增加了一行?
我們再來查詢實體視圖的信息?
SQL> select * from xjzhang_mat_view;?
A ? ? ? ? ?B?
---------- ----------?
aaa ? ? ? ?1?
bbb ? ? ? ?2?
可以看出實體視圖的信息沒有發生變化，因為我們在創建實體視圖的時候，我們沒有指定該視圖的刷新方法和刷新模式，所以創建完該實體視圖，該視圖默認的刷新方法和刷新模式為 FORCE DEMAND?
我們可以通過 dba_mviews 這個視圖查詢我們創建的實體視圖的信息?
SQL> select a.mview_name,a.refresh_mode,a.refresh_method from dba_mviews a where a.mview_name='XJZHANG_MAT_VIEW';?
MVIEW_NAME ? ? ? ? ? ? ? ? ? ? REFRESH_MODE ? REFRESH_METHOD?
------------------------------ -------------- --------------?
XJZHANG_MAT_VIEW ? ? ? ? ? ? ? DEMAND ? ? ? ? FORCE?
這里默認的是手工刷新,所以在這里我們對實體視圖進行更新?
SQL> EXEC DBMS_MVIEW.REFRESH('XJZHANG_MAT_VIEW')?
PL/SQL 過程已成功完成。?
然后我們再次查詢該實體視圖?
SQL> SELECT * FROM XJZHANG_MAT_VIEW;?
A ? ? ? ? ?B?
---------- ----------?
aaa ? ? ? ?1?
bbb ? ? ? ?2?
ccc ? ? ? ?3?
這說明了基表的數據發生變化，那么實體視圖的內容也將被寫入到對應的存儲空間中。?
我們也可以創建自動更新的實體視圖,同樣我們創建一張表?
SQL> create table xjzhang_table4 (a varchar2(10),b number(5));?
表已創建。?
SQL> insert into xjzhang_table4 values ('aaa','00001');?
已創建 1 行。?
SQL> commit;?
提交完成。?
然后我們創建實體視圖?
SQL> create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4;?
create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4?
*?
第 1 行出現錯誤: ORA-12054: 無法為實體化視圖設置 ON COMMIT 刷新屬性?
物化視圖的快速刷新采用了增量的機制，在刷新時，只針對基表上發生變化的數據進行刷新。因此快速刷新是物化視圖刷新方式的首選。但是快速刷新具有較多的約束，而且對于采用ON COMMIT模式進行快速刷新的物化視圖更是如此。對于包含聚集和包含連接的物化視圖的快速刷新機制并不相同，而且對于多層嵌套的物化視圖的快速刷新更是有額外的要求。如此多的限制一般很難記全，當建立物化視圖失敗時，
Oracle
給出的錯誤信息又過于簡單，有時無法使你準確定位到問題的原因。Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW過程可以幫助你快速定位問題的原因。?
Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW過程可以幫助你快速定位問題的原因,我們來試一下ORACLE 提供的包.?
使用EXPLAIN_MVIEW過程首先要建立MV_CAPABILITIES_TABLE表?
創建步驟如下?
我們執行一個腳本來完成創建?
SQL> @?/RDBMS/ADMIN/utlxmv.sql?
表已創建。?
然后我們執行這個包?
SQL> begin?
2 dbms_mview.explain_mview('select * from xjzhang_table4');?
3 end;?
4?
/?
PL/SQL 過程已成功完成。?
然后我們通過 select capability_name, possible, msgtxt from mv_capabilities_table where capability_name like 'REFRESH%'這個腳本來查詢結果?
SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME ? ? ? ? ? ? ? ? P MSGTXT?
------------------------------ ?- ------------------------------ REFRESH_COMPLETE ? ? ? ? ? ? ? ?N 主表中沒有任何主鍵約束條件 REFRESH_FAST ? ? ? ? ? ? ? ? ? ?N?
REFRESH_FAST_AFTER_INSERT ? ? ? N 詳細信息表沒有實體化視圖日志?
REFRESH_FAST_AFTER_ONETAB_DML ? N 查看禁用 REFRESH_FAST_AFTER_IN SERT 的原因?
REFRESH_FAST_AFTER_ANY_DML ? ? ?N 查看禁用 REFRESH_FAST_AFTER_ON ETAB_DML 的原因 REFRESH_FAST_PCT N PCT 不可能在實體化視圖中的任何從表上?
已選擇6行。?
我們可以看到第一條?
REFRESH_COMPLETE N 主表中沒有任何主鍵約束條件?
我們給 xjzhang_table4 創建主鍵?
SQL> alter table xjzhang_table4 add (constraint xjzhang_pri primary key (b));?
表已更改。?
然后我們再次創建實體視圖?
SQL> create materialized view xjzhang_mat_view1 refresh force on commit as select * from xjzhang_table4;?
實體化視圖已創建。?
可以看已經成功創建,我們現在向表中插入數據來查看實體視圖的變化情況?
我們首先查詢一下表中的記錄和實體視圖中的記錄?
SQL> select * from xjzhang_table4;?
A ? ? ? ? ?B?
---------- ----------?
aaa ? ? ? ?1?
SQL> select * from xjzhang_mat_view1; ?
A ? ? ? ? ?B?
---------- ----------?
aaa ? ? ? ?1?
然后我們向表中插入 一條記錄?
SQL> insert into xjzhang_table4 values ('afd','00002');?
已創建 1 行。?
SQL> commit;?
提交完成。?
我們再來查詢一下實體視圖的內容?
SQL> select * from xjzhang_mat_view1;?
A ? ? ? ? ?B?
---------- ----------?
aaa ? ? ? ?1?
物化視圖為什么沒有變化?
刷新（Refresh）：指當基表發生了DML操作后，物化視圖何時采用哪種方式和基表進行同步。刷新的模式有兩種：ON DEMAND和ON COMMIT。ON DEMAND指物化視圖在用戶需要的時候進行刷新，可以手工通過DBMS_MVIEW.REFRESH等方法來進行刷新，也可以通過JOB定時進行刷新。ON COMMIT指出物化視圖在對基表的DML操作提交的同時進行刷新。刷新的方法有四種：FAST、COMPLETE、FORCE和NEVE*。FAST刷新采用增量刷新，只刷新自上次刷新以后進行的修改。COMPLETE刷新對整個物化視圖進行完全的刷新。如果選擇FORCE方式，則Oracle在刷新時會去判斷是否可以進行快速刷新，如果可以則采用FAST方式，否則采用COMPLETE的方式。NEVER指物化視圖不進行任何刷新。默認值是FORCE ON DEMAND?
通過上面的這段話我們知道,刷新的類型 一般有兩種：ON DEMAND 和 ON COMMIT?
ON DEMAND指物化視圖在用戶需要的時候進行刷新，可以手工通過DBMS_MVIEW.REFRESH等方法來進行刷新,這種方法也就是我們長說的使用手工刷新,而ON COMMIT也就是我們長說的 自動刷新,而刷新的方法有四種FAST、COMPLETE、FORCE和NEVER?
我們選擇的是FORCE 說明ORACLE 是有選擇性的刷新,如果可以采用FAST,要不才用COMPLETE?
我們采用手工刷新?
EXECUTE DBMS_MVIEW.REFRESH('xjzhang_mat_view1','C');?
C 代表 完全刷新?
F 代表 快速刷新和強制刷新?
SQL> select * from xjzhang_mat_view1;?
A ? ? ? ? ?B?
---------- ----------?
aaa ? ? ? ?1?
SQL> EXECUTE DBMS_MVIEW.REFRESH('xjzhang_mat_view1','C');?
PL/SQL 過程已成功完成。?
SQL> select * from xjzhang_mat_view1;?
A ? ? ? ? ?B?
---------- ----------?
aaa ? ? ? ?1?
afd ? ? ? ?2?
我們刪除該實體視圖?
SQL> drop materialized view xjzhang_mat_view1;?
實體化視圖已刪除。?
然后我們重新創建實體視圖采用 FAST 方法?
SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4;?
create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4?
*?
第 1 行出現錯誤: ORA-23413: 表 "SYS"."XJZHANG_TABLE4" 不帶實體化視圖日志?
錯誤提示需要帶實體化視圖日志?
我們來創建實體化視圖日志?
SQL> create materialized view log on xjzhang_table4 with rowid, sequence (a, b) including new values;?
實體化視圖日志已創建。?
然后我們再次創建實體視圖 FAST 方法?
SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4;?
create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4?
*?
第 1 行出現錯誤: ORA-23415: "SYS"."XJZHANG_TABLE4" 的實體化視圖日志不記錄主鍵?
又提示錯誤，根據提示錯誤我們主鍵失效?
SQL> alter table xjzhang_table4 modify constraint xjzhang_pri disable;?
表已更改。?
然后我們再次創建物化視圖 FAST?
SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4;?
create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4?
*?
第 1 行出現錯誤: ORA-12014: 表 'XJZHANG_TABLE4' 不包含主鍵約束條件?
這次
系統
又提示不包含主鍵約束條件，我們刪除實體視圖對應的日志?
SQL> DROP MATERIALIZED VIEW LOG ON xjzhang_table4;?
實體化視圖日志已刪除。?
我們在創建實體視圖日志的時候設定主鍵?
SQL> create MATERIALIZED VIEW LOG ON xjzhang_table4 WITH PRIMARY KEY; 實體化視圖日志已創建。?
然后我們再次創建實體視圖?
SQL> create materialized view xjzhang_mat_view1 refresh fast on commit as select * from xjzhang_table4;?
實體化視圖已創建。?
可以看出創建成功，如果需要自動更新的話，我們需要創建一個自動執行的 JOB。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
