materialized view基礎知識

一、------------------------------------------------------------------------------------------ 物化檢視是包括一個查詢結果的資料庫對像，它是遠端資料的的本地副本，或者用來生成基於資料表求和的彙總表。物化檢視儲存基於遠端表的資料，也可以稱為快照。 物化檢視可以查詢表，檢視和其它的物化檢視。 通常情況下，物化檢視被稱為主表（在複製期間）或明細表（在資料倉儲中）。 對於複製，物化檢視允許你在本地維護遠端資料的副本,這些副本是隻讀的。如果你想修改本地副本，必須用高階複製的功能。當你想從一個表或檢視中抽取資料時，你可以用從物化檢視中抽取。 對於資料倉儲，建立的物化檢視通常情況下是聚合檢視，單一表聚合檢視和連線檢視。 本篇我們將會看到怎樣建立物化檢視並且討論它的重新整理選項。[@more@]在複製環境下，建立的物化檢視通常情況下主鍵，rowid,和子查詢檢視。 1.主鍵物化檢視： 下面的語法在遠端資料庫表emp上建立主鍵物化檢視 SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/48 WITH PRIMARY KEY AS SELECT * FROM emp@remote_db; Materialized view created. 注意：當用FAST選項建立物化檢視，必須建立基於主表的檢視日誌,如下: SQL> CREATE MATERIALIZED VIEW LOG ON emp; Materialized view log created.2.Rowid物化檢視 下面的語法在遠端資料庫表emp上建立Rowid物化檢視 SQL> CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID AS SELECT * FROM emp@remote_db; Materialized view log created. 3.子查詢物化檢視 下面的語法在遠端資料庫表emp上建立基於emp和dept表的子查詢物化檢視 SQL> CREATE MATERIALIZED VIEW mv_empdept AS SELECT * FROM emp@remote_db e WHERE EXISTS (SELECT * FROM dept@remote_db d WHERE e.dept_no = d.dept_no) Materialized view log created. REFRESH 子句 [refresh [fast|complete|force] [on demand | commit] [start with date] [next date] [with {primary key|rowid}]] Refresh選項說明: a. oracle用重新整理方法在物化檢視中重新整理資料. b. 是基於主鍵還是基於rowid的物化檢視 c. 物化檢視的重新整理時間和間隔重新整理時間 Refresh方法-FAST子句 增量重新整理用物化檢視日誌（參照上面所述）來傳送主表已經修改的資料行到物化檢視中.如果指定REFRESH FAST子句，那麼應該對主表建立物化檢視日誌 SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created. 對於增量重新整理選項，如果在子查詢中存在分析函式，則物化檢視不起作用。 Refresh方法- COMPLETE子句 完全重新整理重新生成整個檢視，如果請求完全重新整理，oracle會完成 完全重新整理即使增量重新整理可用。 Refresh Method – FORCE 子句 當指定FORCE子句，如果增量重新整理可用Oracle將完成增量重新整理，否則將完成完全重新整理,如果不指定重新整理方法(FAST, COMPLETE, or FORCE),Force選項是預設選項 主鍵和ROWD子句 WITH PRIMARY KEY選項生成主鍵物化檢視,也就是說物化檢視是基於主表的主鍵，而不是ROWID(對應於ROWID子句). PRIMARY KEY是預設選項,為了生成PRIMARY KEY子句，應該在主表上定義主鍵，否則應該用基於ROWID的物化檢視. 主鍵物化檢視允許識別物化檢視主表而不影響物化檢視增量重新整理的可用性。 Rowid物化檢視只有一個單一的主表，不能包括下面任何一項: n Distinct 或者聚合函式. n Group by，子查詢，連線和SET操作 重新整理時間 START WITH子句通知資料庫完成從主表到本地表第一次複製的時間,應該及時估計下一次執行的時間點, NEXT 子句說明了重新整理的間隔時間. SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 2 WITH PRIMARY KEY AS SELECT * FROM emp@remote_db; Materialized view created. 在上面的例子中，物化檢視資料的第一個副本在建立時生成，以後每兩天重新整理一次. 總結 物化檢視提供了可伸縮的基於主鍵或ROWID的檢視,指定了重新整理方法和自動重新整理的時間。 二、------------------------------------------------------------------------------------------ Oracle的物化檢視提供了強大的功能，可以用於預先計算並儲存表連線或聚集等耗時較多的操作的結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結果。 物化檢視有很多方面和索引很相似：使用物化檢視的目的是為了提高查詢效能；物化檢視對應用透明，增加和刪除物化檢視不會影響應用程式中SQL語句的正確性和有效性；物化檢視需要佔用儲存空間；當基表發生變化時，物化檢視也應當重新整理。 物化檢視可以分為以下三種型別：包含聚集的物化檢視；只包含連線的物化檢視；巢狀物化檢視。 三種物化檢視的快速重新整理的限制條件有很大區別，而對於其他方面則區別不大。建立物化檢視時可以指定多種選項，下面對幾種主要的選擇進行簡單說明： 建立方式（Build Methods）：包括BUILD IMMEDIATE和BUILD DEFERRED兩種。BUILD IMMEDIATE是在建立物化檢視的時候就生成資料，而BUILD DEFERRED則在建立時不生成資料，以後根據需要在生成資料。預設為BUILD IMMEDIATE。 查詢重寫（Query Rewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE兩種。分別指出建立的物化檢視是否支援查詢重寫。查詢重寫是指當對物化檢視的基表進行查詢時，Oracle會自動判斷能否透過查詢物化檢視來得到結果，如果可以，則避免了聚集或連線操作，而直接從已經計算好的物化檢視中讀取資料。預設為DISABLE QUERY REWRITE。 重新整理（Refresh）：指當基表發生了DML操作後，物化檢視何時採用哪種方式和基表進行同步。重新整理的模式有兩種：ON DEMAND和ON COMMIT。ON DEMAND指物化檢視在使用者需要的時候進行重新整理，可以手工透過DBMS_MVIEW.REFRESH等方法來進行重新整理，也可以透過JOB定時進行重新整理。ON COMMIT指出物化檢視在對基表的DML操作提交的同時進行重新整理。重新整理的方法有四種：FAST、COMPLETE、FORCE和NEVE*。**ST重新整理採用增量重新整理，只重新整理自上次重新整理以後進行的修改。COMPLETE重新整理對整個物化檢視進行完全的重新整理。如果選擇FORCE方式，則Oracle在重新整理時會去判斷是否可以進行快速重新整理，如果可以則採用FAST方式，否則採用COMPLETE的方式。NEVER指物化檢視不進行任何重新整理。預設值是FORCE ON DEMAND。 在建立物化檢視的時候可以指定ORDER BY語句，使生成的資料按照一定的順序進行儲存。不過這個語句不會寫入物化檢視的定義中，而且對以後的重新整理也無效。 物化檢視日誌：如果需要進行快速重新整理，則需要建立物化檢視日誌。物化檢視日誌根據不同物化檢視的快速重新整理的需要，可以建立為ROWID或PRIMARY KEY型別的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。 可以指明ON PREBUILD TABLE語句將物化檢視建立在一個已經存在的表上。這種情況下，物化檢視和表必須同名。當刪除物化檢視時，不會刪除同名的表。這種物化檢視的查詢重寫要求引數QUERY_REWRITE_INTEGERITY必須設定為trusted或者stale_tolerated。 物化檢視可以進行分割槽。而且基於分割槽的物化檢視可以支援分割槽變化跟蹤（PCT）。具有這種特性的物化檢視，當基表進行了分割槽維護操作後，仍然可以進行快速重新整理操作。對於聚集物化檢視，可以在GROUP BY列表中使用CUBE或ROLLUP，來建立不同等級的聚集物化檢視。 物化檢視的基本操作和使用可以檢視網址：http://blog.itpub.net/post/468/13318 相關的東東。我主要說明一下使用物化檢視的基本東東。如如何建立在特定的表空間上，這些在其他的物化檢視上面幾乎都沒有任何介紹的。主要以我做的一個例子來操作， 如果對物化檢視的基本概念清楚了就比較明白在那裡寫特定的表空間儲存了。 建立物化檢視時應先建立儲存的日誌空間 CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv tablespace ZGMV_DATA --日誌儲存在特定的表空間 WITH ROWID ; CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata tablespace ZGMV_DATA --日誌儲存在特定的表空間 WITH ROWID,sequence(LEVYDETAILDATAID); CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata tablespace ZGMV_DATA --日誌儲存在特定的表空間 WITH rowid,sequence(LEVYDATAID); 然後建立物化檢視 --建立物化檢視 create materialized view MV_LVY_LEVYDETAILDATA TABLESPACE ZGMV_DATA --儲存表空間 BUILD DEFERRED --延遲重新整理不立即重新整理 refresh force --如果可以快速重新整理則進行快速重新整理，否則完全重新整理 on demand --按照指定方式重新整理 start with to_date('24-11-2005 18:00:10', 'dd-mm-yyyy hh24:mi:ss') --第一次重新整理時間 next TRUNC(SYSDATE+1)+18/24 --重新整理時間間隔 as SELECT levydetaildataid, detaildatano, taxtermbegin, taxtermend, ...... ROUND(taxdeduct * taxpercent1, 2) - ROUND(taxdeduct * taxpercent2, 2) - ROUND(taxdeduct * taxpercent3, 2) - ROUND(taxdeduct * taxpercent4, 2) - ROUND(taxdeduct * taxpercent5, 2) taxdeduct, ROUND(taxfinal * taxpercent1, 2) - ROUND(taxfinal * taxpercent2, 2) - ROUND(taxfinal * taxpercent3, 2) - ROUND(taxfinal * taxpercent4, 2) - ROUND(taxfinal * taxpercent5, 2) taxfinal, a.levydataid, a.budgetitemcode, taxtypecode, ...... FROM tb_lvy_levydetaildata a, tb_lvy_levydata c, MV_LVY_LEVYTAXBGTDIV b WHERE a.levydataid = c.levydataid AND a.budgetdistrscalecode = b.budgetdistrscalecode AND a.budgetitemcode = b.budgetitemcode AND c.incomeresidecode = b.rcvfisccode AND C.TAXSTATUSCODE='08' AND C.NEGATIVEFLAG!='9' 刪除物化檢視日誌 --刪除物化檢視： --刪除日誌： DROP materialized view log on mv_lvy_levytaxbgtdiv; DROP materialized view log on tb_lvy_levydetaildata; DROP materialized view log on tb_lvy_levydata; --刪除物化檢視 drop materialized view MV_LVY_LEVYDETAILDATA; --基本和對錶的操作一致 --物化檢視由於是物理真實存在的，故可以建立索引。 建立方式和對普通表建立方式相同，就不在重複寫了。 三、------------------------------------------------------------------------------------------ 物化檢視對於前臺資料庫使用者來說如同一個實際的表,具有和一般表相通的如select等操作,而其實際上是一個檢視,一個由系統實現定期重新整理其資料的檢視(具體重新整理時間在定義物化檢視的時候已有定義),使用物化檢視更可以實現檢視的所有功能,而物化檢視確不是在使用時才讀取,大大提高了讀取速度,特別適用抽取大資料量表某些資訊以及資料鏈連線表使用.具體語法如下: 物化檢視對於前臺資料庫使用者來說如同一個實際的表,具有和表相通的一般select操作,而其實際上是一個檢視,一個定期重新整理資料的檢視(具體重新整理時間在定義物化檢視的時候已有定義),使用物化檢視可以實現檢視的所有功能,而物化檢視確不是在使用時才讀取,大大提高了讀取速度,特別適用抽取大資料量表某些資訊以及資料鏈連線表使用.具體語法如下: CREATE MATERIALIZED VIEW an_user_base_file_no_charge REFRESH COMPLETE START WITH SYSDATE NEXT TRUNC(SYSDATE+29)+5.5/24 as select distinct user_no from cw_arrearage t where (t.mon = dbms_tianjin.getLastMonth or t.mon = add_months(dbms_tianjin.getLastMonth, -1)) drop materialized view an_user_base_file_no_charge; 第 13 章 物化檢視 　　8.1.5企業版/個人版開始支援 　　需要許可權：GRANT CREATE MATERIALIZED VIEW，還必須直接賦予GRANT QUERY REWRITE.為實現查詢重寫，必須使用CBO. 　　13.1 物化檢視如何工作 　　設定 　　COMPATIBLE引數必須高於8.1.0 QUERY_REWRITE_ENABLED = TRUE QUERY_REWRITE_INTEGRETY = ENFORCED - 查詢僅用Oracle強制與保證的約束、規則重寫； TRUSTED – 查詢除用Oracle強制與保證的約束、規則，也可用使用者設定的資料間的任何關係來重寫； STALE_TOLERATED – 即便Oracle知道物化檢視中資料過期（與事實表等不同步），也重寫查詢。 　　建立物化檢視的使用者必須具有直接賦予的GRANT QUERY REWRITE許可權，不能透過角色繼承。 　　內部機制 　　全文匹配 　　部分匹配：從FROM子句開始，最佳化器比較之後的文字，然後比較SELECT列表 　　一般重寫方法： 　　資料充分 　　關聯相容 　　分組相容 　　聚集相容 　　13.2 確保使用物化檢視 　　約束 　　考慮到現實環境的資料量，可以將主鍵、外來鍵、非空等約束置為NOVALIDATE，並調整QUERY_REWRITE_INTEGRITY為TRUSTED，這樣可以達到“欺騙”資料庫的目的，但必須注意如果無法保證此類約束的真實有效，查詢改寫後可能造成結果不精確。 　　維度 　　實際就是指明已存在的表中各列的歸併關係，從而關聯事實表後形成的物化檢視可用於向“上”歸併（相當於用表中代表更高歸併關係的列關聯事實表）。標準語法： CREATE DIMENSION time_hierarchy_dim LEVEL day IS time_hierarchy.day LEVEL mmyyyy IS time_hierarchy.mmyyyy LEVEL yyyy IS time_hierarchy.yyyy HIERARCHY time_rollup (day CHILD OF mmyyyy CHILD OF yyyy) ATTRIBUTE mmyyyy DETERMINES mon_yyyy; 　　13.3 DBMS_OLAP 　　估計（物化檢視）大小 　　DBMS_OLAP.ESTIMATE_SUMMARY_SIZE（檢視名， 檢視定義， 估計行數， 估計位元組數）； 　　其中後兩個引數為NUMBER型輸出引數。 　　維度有效性檢查 　　DBMS_OLAP.VALIDATE_DIMENSION（檢視名， 使用者名稱， FALSE， FALSE）； 　　SELECT * FROM 維度表名 　　WHERE ROWIN IN （SEELCT bad_rowid FROM MVIEW$_EXCEPTION）； 　　所選出行即為不符合維度定義的行。 　　推薦物化檢視 　　首先必須新增合適的外來鍵，包透過外來鍵來判定表之間的關係而不是維度。 　　DBMS_OLAP.RECOMMEND_MV（事實表名， 1000000000， ‘’）； 　　第二個參數列示物化檢視可用的空間大小，可傳入一個較大的數。第三個引數傳入需要保留的特定物化檢視，傳入空即為不考慮其他物化檢視。 執行C：oracleRDBMSdemosadvdemo後執行： 　　DEMO_SUMADV.PRETTYPRINT_RECOMMENDATIONS 　　13.4 最後說明 　　物化檢視不為OLTP系統設計 　　在事實表等更新時會導致物化檢視行鎖，從而影響系統併發性。 四、------------------------------------------------------------------------------------------ 定位導致物化檢視無法快速重新整理的原因 =========================================================== 物化檢視的快速重新整理採用了增量的機制，在重新整理時，只針對基表上發生變化的資料進行重新整理。因此快速重新整理是物化檢視重新整理方式的首選。 但是快速重新整理具有較多的約束，而且對於採用ON COMMIT模式進行快速重新整理的物化檢視更是如此。對於包含聚集和包含連線的物化檢視的快速重新整理機制並不相同，而且對於多層巢狀的物化檢視的快速重新整理更是有額外的要求。 如此多的限制一般很難記全，當建立物化檢視失敗時，Oracle給出的錯誤資訊又過於簡單，有時無法使你準確定位到問題的原因。 Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW過程可以幫助你快速定位問題的原因。下面透過一個例子來說明，如果透過這個過程來解決問題。 建立一個快速重新整理的巢狀物化檢視： SQL> CREATE TABLE B (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30)); 表已建立。 SQL> CREATE TABLE C (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30)); 表已建立。 SQL> CREATE TABLE A (ID NUMBER, BID NUMBER, CID NUMBER, NUM NUMBER, 2 CONSTRAINT FK_A_B_BID FOREIGN KEY (BID) REFERENCES B(ID), 3 CONSTRAINT FK_A_C_BID FOREIGN KEY (CID) REFERENCES C(ID)); 表已建立。 SQL> INSERT INTO B SELECT ROWNUM, 'B'||ROWNUM FROM USER_TABLES WHERE ROWNUM INSERT INTO C SELECT ROWNUM, 'C'||ROWNUM FROM USER_TABLES WHERE ROWNUM INSERT INTO A SELECT ROWNUM, TRUNC((ROWNUM - 1)/2) + 1, TRUNC((ROWNUM - 1)/3) + 1, ROWNUM 2 FROM USER_TABLES 3 WHERE ROWNUM COMMIT; 提交完成。 上面建立好基表，下面建立第一層物化檢視。 SQL> CREATE MATERIALIZED VIEW LOG ON A WITH ROWID; 實體化檢視日誌已建立。 SQL> CREATE MATERIALIZED VIEW LOG ON B WITH ROWID; 實體化檢視日誌已建立。 SQL> CREATE MATERIALIZED VIEW LOG ON C WITH ROWID; 實體化檢視日誌已建立。 SQL> CREATE MATERIALIZED VIEW MV_ABC REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS 2 SELECT C.ID CID, C.NAME CNAME, B.ID BID, B.NAME BNAME, A.NUM, 3 A.ROWID AROWID, B.ROWID BROWID, C.ROWID CROWID 4 FROM A, B, C WHERE A.BID = B.ID AND A.CID = C.ID; 實體化檢視已建立。 第一次物化檢視已經建立成功，下面建立巢狀物化檢視： SQL> CREATE MATERIALIZED VIEW LOG ON MV_ABC WITH ROWID (BNAME, CNAME, NUM) INCLUDING NEW VALUES; 實體化檢視日誌已建立。 SQL> CREATE MATERIALIZED VIEW MV_MV_ABC REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS 2 SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROM MV_ABC 3 GROUP BY CNAME, BNAME; SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROM MV_ABC * ERROR 位於第 2 行: ORA-12053: 這不是一個有效的巢狀實體化檢視 錯誤出現了，不過錯誤的描述包含的資訊量並不大。我們看看Oracle的文件上是如何描述這個錯誤的。 ORA-12053 this is not a valid nested materialized view Cause: The list of objects in the FROM clause of the definition of this materialized view had some dependencies upon each other. Action: Refer to the documentation to see which types of nesting are valid. 文件上的描述也是十分籠統的，並沒有指出具體問題所在。 接下來，我們透過使用DBMS_MVIEW.EXPLAIN_MVIEW過程來定位錯誤。 使用EXPLAIN_MVIEW過程首先要建立MV_CAPABILITIES_TABLE表，建表的腳步是$ORACLE_HOME/rdbms/admin/utlxmv.sql。（EXPLAIN_MVIEW過程是兩個過程的過載，一個輸出到MV_CAPABILITIES_TABLE表，另一個以PL/SQL的VARRAY格式輸出，為了簡單起見，我們建立MV_CAPABILITIES_TABLE表）。 SQL> @?rdbmsadminutlxmv.sql 表已建立。 下面簡單研究一下EXPLAIN_MVIEW過程。 DBMS_MVIEW.EXPLAIN_MVIEW(mv IN VARCHAR2, Statement_id IN VARCHAR2:= NULL); 該過程可以輸入已經存在的物化檢視名稱（或USER_NAME.MV_NAME），也可輸入建立物化檢視的查詢語句。另外一個引數STATEMENT_ID輸入一個語句ID，為了標識出表中對應的記錄。 SQL> BEGIN 2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECT CNAME, BNAME, COUNT(*) COUNT, SUM(NUM) SUM_NUM FROM MV_ABC 3 GROUP BY CNAME, BNAME', 'MV_MV_ABC'); 4 END; 5 / PL/SQL 過程已成功完成。 SQL> SELECT CAPABILITY_NAME, RELATED_TEXT, MSGTXT FROM MV_CAPABILITIES_TABLE 2 WHERE STATEMENT_ID = 'MV_MV_ABC' AND POSSIBLE = 'N' AND CAPABILITY_NAME NOT LIKE '%PCT%'; CAPABILITY_NAME RELATED_TEXT MSGTXT ------------------------------ --------------- -------------------------------------------------- REFRESH_FAST_AFTER_ONETAB_DML SUM_NUM 使用 SUM(expr) 時, 未提供 COUNT(expr) REFRESH_FAST_AFTER_ANY_DML YANGTK.MV_ABC mv 日誌沒有序列號 REFRESH_FAST_AFTER_ANY_DML 檢視禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因 根據上面的資訊，已經可以確定問題的原因了，對於聚集物化檢視，使用了SUM(COLUMN)，但是沒有包括COUNT(COLUMN)。 修改物化檢視，重新建立： SQL> CREATE MATERIALIZED VIEW MV_MV_ABC REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS 2 SELECT CNAME, BNAME, COUNT(*) COUNT, COUNT(NUM) NUM_COUNT, SUM(NUM) SUM_NUM FROM MV_ABC 3 GROUP BY CNAME, BNAME; 實體化檢視已建立