oracle中普通視圖和實體試圖的區別

標籤：

Oracle普通視圖和實體化視圖比較來源：茂盛部落格|2013-07-30Oracle普通視圖和實體化視圖比較相對於普通的視圖來說，實體化視圖的不同之處在於實體化視圖管理儲存資料，佔據資料庫的物理空間。實體化視圖的結果會儲存在一個普通的資料表中，在對實體化視圖進行查詢的時候不再會對建立實體化視圖的基表進行查詢，而是直接查詢實體化視圖對應的結果表，然後通過週期性重新整理機制來更新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。

oracle中普通視圖和實體試圖的區別