oracle快照詳解

1、Oracle一.理論部分Oracle的物化視圖是包括一個查詢結(jié)果的數(shù)據(jù)庫對像，它是遠程數(shù)據(jù)的的本地副本，或者用來生成基于數(shù)據(jù)表求和的匯總表。物化視圖存儲基于遠程表的數(shù)據(jù)，也可以稱為快照。Oracle的物化視圖提供了強大的功能，可以用于預(yù)先計算并保存表連接或聚集等耗時較多的操作的結(jié)果，這樣，在執(zhí)行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結(jié)果。物化視圖有很多方面和索引很相似：使用物化視圖的目的是為了提高查詢性能；物化視圖對應(yīng)用透明，增加和刪除物化視圖不會影響應(yīng)用程序中SQL語句的正確性和有效性；物化視圖需要占用存儲空間；當(dāng)基表發(fā)生變化時，物化視圖也應(yīng)當(dāng)刷新。物化視圖可以查詢表，視圖和其

2、它的物化視圖。通常情況下，物化視圖被稱為主表（在復(fù)制期間）或明細表（在數(shù)據(jù)倉庫中）。對于復(fù)制，物化視圖允許你在本地維護遠程數(shù)據(jù)的副本,這些副本是只讀的。如果你想修改本地副本，必須用高級復(fù)制的功能。當(dāng)你想從一個表或視圖中抽取數(shù)據(jù)時，你可以用從物化視圖中抽取。對于數(shù)據(jù)倉庫，創(chuàng)建的物化視圖通常情況下是聚合視圖，單一表聚合視圖和連接視圖。在復(fù)制環(huán)境下，創(chuàng)建的物化視圖通常情況下主鍵，rowid，和子查詢視圖。REFRESH子句refreshfast|complete|forceondemand|commitstartwithdatenextdatewithprimarykey|rowid默認情況下，如果

3、沒指定刷新方法和刷新模式，則Oracle默認為FORCE和DEMAND。物化視圖可以分為以下三種類型：包含聚集的物化視圖；只包含連接的物化視圖；嵌套物化視圖。三種物化視圖的快速刷新的限制條件有很大區(qū)別，而對于其他方面則區(qū)別不大。創(chuàng)建物化視圖時可以指定多種選項，下面對幾種主要的選擇進行簡單說明：倉U建方式（BuildMethods）:包括BUILDIMMEDIATE和BUILDDEFERRED兩種。BUILDIMMEDIATE是在創(chuàng)建物化視圖的時候就生成數(shù)據(jù)，而BUILDDEFERRED則在創(chuàng)建時不生成數(shù)據(jù)，以后根據(jù)需要在生成數(shù)據(jù)。默認為BUILDIMMEDIATE。查詢重寫（QueryRewr

4、ite）:包括ENABLEQUERYREWRITE和DISABLEQUERYREWRITE兩種。分別指出創(chuàng)建的物化視圖是否支持查詢重寫。查詢重寫是指當(dāng)對物化視圖的基表進行查詢時，Oracle會自動判斷能否通過查詢物化視圖來得到結(jié)果，如果可以，則避免了聚集或連接操作，而直接從已經(jīng)計算好的物化視圖中讀取數(shù)據(jù)。默認為DISABLEQUERYREWRITE。刷新（Refresh）：指當(dāng)基表發(fā)生了DML操作后，物化視圖何時采用哪種方式和基表進行同步。刷新的模式有兩種：ONDEMAND和ONCOMMIT。ONDEMAND和ONCOMMIT物化視圖的區(qū)別在于其刷新方法的不同，ONDEMAND指物化視圖在用戶

5、需要的時候進行刷新，可以手工通過DBMS_MVIEW.REFRESH等方法來進行刷新，也可以通過JOB定時進行刷新，即更新物化視圖，以保證和基表數(shù)據(jù)的一致性；而ONCOMMIT是說，一旦基表有了COMMIT，即事務(wù)提交，則立刻刷新，立刻更新物化視圖，使得數(shù)據(jù)和基表一致。對基表，平常的COMMIT在0.01秒內(nèi)可以完成，但在有了ONCOMMIT視圖后，居然要6秒。速度減低了很多倍。ONCOMMIT視圖對基表的影響可見一斑。刷新的方法有四種：FAST、COMPLETE、FORCE和NEVER。1. FAST:增量刷新用物化視圖日志來發(fā)送主表已經(jīng)修改的數(shù)據(jù)行到物化視圖中如果指定REFRESHFAST

6、子句，那么應(yīng)該對主表創(chuàng)建物化視圖日志,故當(dāng)用FAST選項創(chuàng)建物化視圖，必須創(chuàng)建基于主表的視圖日志。SQL>CREATEMATERIALIZEDVIEWLOGONemp;Materializedviewlogcreated.對于增量刷新選項，如果在子查詢中存在分析函數(shù)，則物化視圖不起作用。2. COMPLETE：刷新對整個物化視圖進行完全的刷新。3. FORCE：當(dāng)指定FORCE子句，如果增量刷新可用Oracle將完成增量刷新，否則將完成完全刷新,如果不指定刷新方法（FAST,COMPLETE,orFORCE）,Force選項是默認選項。4. NEVER：指物化視圖不進行任何刷新。默認值是

7、FORCEONDEMAND。在建立物化視圖的時候可以指定ORDERBY語句,使生成的數(shù)據(jù)按照一定的順序進行保存。不過這個語句不會寫入物化視圖的定義中，而且對以后的刷新也無效。物化視圖日志：如果需要進行快速刷新，則需要建立物化視圖日志。物化視圖日志根據(jù)不同物化視圖的快速刷新的需要，可以建立為ROWID或PRIMARYKEY類型的。還可以選擇是否包括SEQUENCE.INCLUDINGNEWVALUES以及指定列的列表。可以指明ONPREBUILDTABLE語句將物化視圖建立在一個已經(jīng)存在的表上。這種情況下，物化視圖和表必須同名。當(dāng)刪除物化視圖時，不會刪除同名的表。這種物化視圖的查詢重寫要求參數(shù)Q

8、UERY_REWRITE_INTEGERITY必須設(shè)置為trusted或者stale_tolerated。物化視圖可以進行分區(qū)。而且基于分區(qū)的物化視圖可以支持分區(qū)變化跟蹤（PCT）。具有這種特性的物化視圖，當(dāng)基表進行了分區(qū)維護操作后，仍然可以進行快速刷新操作。對于聚集物化視圖，可以在GROUPBY列表中使用CUBE或ROLLUP，來建立不同等級的聚集物化視圖。主鍵和ROWID子句：WITHPRIMARYKEY選項生成主鍵物化視圖,也就是說物化視圖是基于主表的主鍵，而不是ROWID（對應(yīng)于ROWID子句）.PRIMARYKEY是默認選項，為了生成PRIMARYKEY子句，應(yīng)該在主表上定義主鍵，否

9、則應(yīng)該用基于ROWID的物化視圖.主鍵物化視圖允許識別物化視圖主表而不影響物化視圖增量刷新的可用性。Rowid物化視圖只有一個單一的主表，不能包括下面任何一項:1. Distinct或者聚合函數(shù).2. Groupby,子查詢，連接和SET操作1主鍵物化視圖：下面的語法在遠程數(shù)據(jù)庫表emp上創(chuàng)建主鍵物化視圖SQL>CREATEMATERIALIZEDVIEWmv_emp_pkREFRESHFASTSTARTWITHSYSDATENEXTSYSDATE+1/48WITHPRIMARYKEYASSELECT*FROMempremotedb;Materializedviewcreated.注意：

10、當(dāng)用FAST選項創(chuàng)建物化視圖，必須創(chuàng)建基于主表的視圖日志,如下：SQL>CREATEMATERIALIZEDVIEWLOGONemp;Materializedviewlogcreated.2. Rowid物化視圖：下面的語法在遠程數(shù)據(jù)庫表emp上創(chuàng)建Rowid物化視圖SQL>CREATEMATERIALIZEDVIEWmv_emp_rowidREFRESHWITHROWIDASSELECT*FROMemp；3. 子查詢物化視圖：下面的語法在遠程數(shù)據(jù)庫表emp上創(chuàng)建基于emp和dept表的子查詢物化視圖SQL>CREATEMATERIALIZEDVIEWmv_empdeptAS

11、SELECT*FROMempeWHEREEXISTS(SELECT*FROMdeptdWHEREe.dept_no=d.dept_no)刷新時間：STARTWITH子句通知數(shù)據(jù)庫完成從主表到本地表第一次復(fù)制的時間，應(yīng)該及時估計下一次運行的時間點,NEXT子句說明了刷新的間隔時間.SQL>CREATEMATERIALIZEDVIEWmv_emp_pkREFRESHFASTSTARTWITHSYSDATENEXTSYSDATE+2WITHPRIMARYKEYASSELECT*FROMemp;在上面的例子中，物化視圖數(shù)據(jù)的第一個副本在創(chuàng)建時生成，以后每兩天刷新一次.creatematerial

12、izedviewMV_LVY_LEVYDETAILDATATABLESPACEZGMV_DATA-保存表空間BUILDDEFERRED-延遲刷新不立即刷新refreshforce-如果可以快速刷新則進行快速刷新，否則完全刷新ondemand-按照指定方式刷新startwithto_date('24-11-200518：00：10','dd-mm-yyyyhh24：mi：ss')-第一次刷新時間nextTRUNC(SYSDATE+1)+18/24-刷新時間間隔asSELECT*FROMemp；物化視圖由于是物理真實存在的，故可以創(chuàng)建索引。二.物化視圖實例物化視圖的

13、快速刷新采用了增量的機制，在刷新時，只針對基表上發(fā)生變化的數(shù)據(jù)進行刷新因此快速刷新是物化視圖刷新方式的首選。但是快速刷新具有較多的約束，而且對于采用ONCOMMIT模式進行快速刷新的物化視圖更是如此。對于包含聚集和包含連接的物化視圖的快速刷新機制并不相同，而且對于多層嵌套的物化視圖的快速刷新更是有額外的要求。如此多的限制一般很難記全，當(dāng)建立物化視圖失敗時，Oracle給出的錯誤信息又過于簡單，有時無法使你準(zhǔn)確定位到問題的原因。Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW過程可以幫助你快速定位問題的原因。下面通過一個例子來說明，如果通過這個過程來解決問題。建立一個快速刷新的

14、嵌套物化視圖：SQL>CREATETABLEB(IDNUMBERPRIMARYKEY,NAMEVARCHAR2(30);表已創(chuàng)建。SQL>CREATETABLEC(IDNUMBERPRIMARYKEY,NAMEVARCHAR2(30);表已創(chuàng)建。SQL>CREATETABLEA(IDNUMBER,BIDNUMBER,CIDNUMBER,NUMNUMBER,2 CONSTRAINTFK_A_B_BIDFOREIGNKEY(BID)REFERENCESB(ID),3 CONSTRAINTFK_A_C_BIDFOREIGNKEY(CID)REFERENCESC(ID);表已創(chuàng)建。S

15、QL>INSERTINTOBSELECTROWNUM,'B'|ROWNUMFROMUSER_TABLESWHEREROWNUM<=6;已創(chuàng)建6行。SQL>INSERTINTOCSELECTROWNUM,'C'|ROWNUMFROMUSER_TABLESWHEREROWNUM<=4;已創(chuàng)建4行。SQL>INSERTINTOASELECTROWNUM,TRUNC(ROWNUM-1)/2)+1,TRUNC(ROWNUM-1)/3)+1,ROWNUM2 FROMUSER_TABLES3 WHEREROWNUM<=12;已創(chuàng)建12行。S

16、QL>COMMIT;提交完成。上面建立好基表，下面建立第一層物化視圖。SQL>CREATEMATERIALIZEDVIEWLOGONAWITHROWID;實體化視圖日志已創(chuàng)建。SQL>CREATEMATERIALIZEDVIEWLOGONBWITHROWID;實體化視圖日志已創(chuàng)建。SQL>CREATEMATERIALIZEDVIEWLOGONCWITHROWID;實體化視圖日志已創(chuàng)建。SQL>CREATEMATERIALIZEDVIEWMV_ABCREFRESHFASTONCOMMITENABLEQUERYREWRITEAS2 SELECTC.IDCID,C.NA

17、MECNAME,B.IDBID,B.NAMEBNAME,A.NUM,3 A.ROWIDAROWID,B.ROWIDBROWID,C.ROWIDCROWID4 FROMA,B,CWHEREA.BID=B.IDANDA.CID=C.ID;實體化視圖已創(chuàng)建。第一次物化視圖已經(jīng)建立成功，下面建立嵌套物化視圖：SQL>CREATEMATERIALIZEDVIEWLOGONMV_ABCWITHROWID(BNAME,CNAME,NUM)INCLUDINGNEWVALUES;實體化視圖日志已創(chuàng)建。SQL>CREATEMATERIALIZEDVIEWMV_MV_ABCREFRESHFASTONCO

18、MMITENABLEQUERYREWRITEAS2SELECTCNAME,BNAME,COUNT(*)COUNT,SUM(NUM)SUM_NUMFROMMV_ABC3GROUPBYCNAME,BNAME;SELECTCNAME,BNAME,COUNT(*)COUNT,SUM(NUM)SUM_NUMFROMMV_ABC*ERROR位于第2行:ORA-12053:這不是一個有效的嵌套實體化視圖錯誤出現(xiàn)了，不過錯誤的描述包含的信息量并不大。我們看看Oracle的文檔上是如何描述這個錯誤的。ORA-12053thisisnotavalidnestedmaterializedviewCause:Thel

19、istofobjectsintheFROMclauseofthedefinitionofthismaterializedviewhadsomedependenciesuponeachother.Action:Refertothedocumentationtoseewhichtypesofnestingarevalid.文檔上的描述也是十分籠統(tǒng)的，并沒有指出具體問題所在。接下來，我們通過使用DBMS_MVIEW.EXPLAIN_MVIEW過程來定位錯誤。使用EXPLAIN_MVIEW過程首先要建立MV_CAPABILITIES_TABLE表，建表的腳步是$ORACLE_HOME/rdbms/ad

20、min/utlxmv.sql。(EXPLAIN_MVIEW過程是兩個過程的重載，一個輸出到MV_CAPABILITIES_TABLE表，另一個以PL/SQL的VARRAY格式輸出，為了簡單起見，我們建立MV_CAPABILITIES_TABLE表)。SQL>?rdbmsadminutlxmv.sql表已創(chuàng)建。下面簡單研究一下EXPLAIN_MVIEW過程。DBMS_MVIEW.EXPLAIN_MVIEW(mvINVARCHAR2,Statement_idINVARCHAR2:=NULL);該過程可以輸入已經(jīng)存在的物化視圖名稱(或USER_NAME.MV_NAME)，也可輸入建立物化視圖的查詢語句。另外一個參數(shù)STATEMENT_ID輸入一個語句ID,為了標(biāo)識出表中對應(yīng)的記錄。SQL>BEGIN2 DBMS_MVIEW.EXPLAIN_MVIEW('SELECTCNAME,BNAME,COUNT(*)COUNT,SUM(NUM)SUM_NUMFROMMV_ABC3 GROUPBYCNAME,BNAME','MV_MV_ABC');4 END;5 /PL/SQL過程已成功完成。SQL>SELECTCAPA