ZT 物化檢視詳解

Oracle8i版本開始提供可以建立實體化檢視即物化檢視(MATERIALIZED VIEW)，它確實存放有物理資料。物化檢視包含定義檢視的查詢時所選擇的基表中的行。對物化檢視的查詢就是直接從該檢視中取出行。

在olap環境中，mview是以空間換時間的一種有效手段，更少的物理讀/寫，更少的cpu時間，更快的響應速度，所以它不適合高階的oltp環境；在oltp環境中，規模較大的報表適合使用mview來提高查詢效能。

資料倉儲中的物化檢視主要用於預先計算並儲存表連線或聚集等耗時較多的操作的結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結果。在資料倉儲中，還經常使用查詢重寫（query rewrite）機制，這樣不需要修改原有的查詢語句，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和NEVER。FAST重新整理採用增量重新整理，只重新整理自上次重新整理以後進行的修改。COMPLETE重新整理對整個物化檢視進行完全的重新整理。如果選擇FORCE方式，則Oracle在重新整理時會去判斷是否可以進行快速重新整理，如果可以則採用FAST方式，否則採用COMPLETE的方式。NEVER指物化檢視不進行任何重新整理。預設值是FORCE ON DEMAND。

顯然快速重新整理是物化檢視重新整理方式的首選。但是，不是所有的物化檢視都可以進行快速重新整理，只有滿足某些條件的物化檢視才具有快速重新整理的能力。

在建立物化檢視的時候可以指定ORDER BY語句，使生成的資料按照一定的順序進行儲存。不過這個語句不會寫入物化檢視的定義中，而且對以後的重新整理也無效。

物化檢視日誌：如果需要進行快速重新整理，則需要建立物化檢視日誌。物化檢視日誌根據不同物化檢視的快速重新整理的需要，可以建立為ROWID或PRIMARY KEY型別的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

可以指明ON PREBUILT TABLE語句將物化檢視建立在一個已經存在的表上。這種情況下，物化檢視和表必須同名。當刪除物化檢視時，不會刪除同名的表。這種物化檢視的查詢重寫要求引數QUERY_REWRITE_INTEGERITY必須設定為trusted或者stale_tolerated。

物化檢視可以進行分割槽。而且基於分割槽的物化檢視可以支援分割槽變化跟蹤（PCT）。具有這種特性的物化檢視，當基表進行了分割槽維護操作後，仍然可以進行快速重新整理操作。

對於聚集物化檢視，可以在GROUP BY列表中使用CUBE或ROLLUP，來建立不同等級的聚集物化檢視。

根據查詢的不同，快速重新整理的限制條件也不相同，下面總結一下不同型別的物化檢視對快速重新整理的限制條件。

可以參考： http://xsb.itpub.net/post/419/54722

所有型別的快速重新整理物化檢視都必須滿足的條件：

1.物化檢視不能包含對不重複表示式的引用，如SYSDATE和ROWNUM；

2.物化檢視不能包含對LONG和LONG RAW資料型別的引用。

只包含連線的物化檢視：

1.必須滿足所有快速重新整理物化檢視都滿足的條件；

2.不能包括GROUP BY語句或聚集操作；

3.如果在WHERE語句中包含外連線，那麼唯一約束必須存在於連線中內表的連線列上；

4.如果不包含外連線，那麼WHERE語句沒有限制，如果包含外連線，那麼WHERE語句中只能使用AND連線，並且只能使用“＝”操作。

5.FROM語句列表中所有表的ROWID必須出現在SELECT語句的列表中。

6.FROM語句列表中的所有表必須建立基於ROWID型別的物化檢視日誌。

1.關鍵內容：

l 實體檢視存放有物理資料；

l 實體檢視背後的查詢只在檢視建立或重新整理時執行，即如果建立後不進行重新整理則只得到建立時的資料；

l 實體檢視使用DBMS_MVIEW 程式包中含有重新整理和管理實體檢視的過程來進行管理；

l 在匯出和匯入（EXP、IMP）中使用MVDATA引數來實現實體檢視資料的匯出和匯入；

l 使用CREATE MATERIALIZED VIEW 語句建立實體檢視；

l 實體檢視中的查詢表叫主表（master tables）（複製項）或詳細表（資料倉儲項）。為一致起見，這些主表叫主資料庫（master databases.）；

l 為了複製目的，實體檢視允許你在本地管理遠端複製；

l 所複製的資料可以使用高階複製特性進行更新；

l 在複製環境下，通常建立的實體檢視都是主鍵、ROWID和子查詢實體檢視。

2.建立物化檢視前提：

l 要有授權建立實體檢視的許可權（CREATE MATERIALIZED VIEW 或CREATE SNAPSHOT）；

l 必須有訪問各個主表的許可權，即 有SELECT ANY TABLE 的系統許可權。

如果在另外的使用者模式下建立實體檢視，則：

l 需要有CREATE ANY MATERIALIZED VIEW或CREATE ANY SNAPSHOT、SELECT ANY TABLE 許可權；

l 必須有CREATE TABLE、SELECT ANY TABLE系統許可權。

如果帶查詢重寫有效來建立實體檢視，則：

l 主表的主人必須有QUERY REWRITE系統許可權；

l 如果你不是主表主人，則必須有GLOBAL QUERY REWRITE系統許可權；

l 如果模式主人沒有主表，則該模式主人必須有GLOBAL QUERY REWRITE許可權。

3.建立實體檢視語法：

下面給出實體檢視的簡單語法：

CREATE MATERIALIZED VIEW [ SNAPSHOT ] [schema.] [materializede_view|snapshot]

[ [ [ segment_attributes_clause|LOB_storage_clause|CACHE|NOCACHE ] |

[ CLUSTER cluster( column1,…) ] ] patitioning_clauses parallel_clause build_clause ] |

[ ON PREBUILT TABLE [WITH | WITHOUT] REDUCED PRECISION ]

USING INDEX [physical_attributes_clause| TABLESPACE tablespace ] refresh_clause

FOR UPDATE [ DISABLE | ENABLE ] QUERY REWRITE AS subquery;

引數的解釋另見《Oracle8I資料庫基礎》。

4.建立實體例子：

例1.建立實體彙總檢視:

下面語句建立一個移植的實體檢視，並指定預設的重新整理方法、模式及時間：

CREATE MATERIALIZED VIEW mv1 REFRESH FAST ON COMMIT

BUILD IMMEDIATE

AS SELECT t.month, p.prod_name, SUM(f.sales) AS sum_sales

FROM time t, product p, fact f

WHERE f.curDate = t.curDate AND f.item = p.item

GROUP BY t.month, p.prod_name;

例2.自動重新整理的實體檢視:

下面語句建立一個複雜的實體檢視all_emps,它查詢DALLAS和BALTIMORE中的職工表:

CREATE MATERIALIZED VIEW all_emps

PCTFREE 5 PCTUSED 60

TABLESPACE users

STORAGE INITIAL 50K NEXT 50K

USING INDEX STORAGE (INITIAL 25K NEXT 25K)

REFRESH START WITH ROUND(SYSDATE + 1) + 11/24

NEXT NEXT_DAY(TRUNC(SYSDATE, ’MONDAY’) + 15/24

AS SELECT * FROM fran.emp@dallas

UNION

SELECT * FROM

Oracle在早上11點自動重新整理，接著就在週一的15點進行重新整理。預設重新整理方法是FORCE，all_emps檢視包含一個UNION，它是不支援快速重新整理的，所以Oracle只能用完全（complete）重新整理。

由於執行實體檢視實際就是資料庫複製，為了使系統能進行復制，需要啟動後臺程式SNP0,…SNP9和SNPA... SNPZ。要啟動後臺程式，就要在INIT.ORA引數檔案中加上下面引數：

JOB_QUEUE_PROCESSES= integer ( integer > = 1 )

實體檢視是新的物件，它的資訊被存放在下面的資料字典中：

1.DBA_MVIEW_AGGREGATES存放實體檢視的基本資訊

SQL> desc dba_mview_aggregates

名稱 空? 型別

----------------------------------------- -------- -------------

OWNER NOT NULL VARCHAR2(30)

MVIEW_NAME NOT NULL VARCHAR2(30)

POSITION_IN_SELECT NOT NULL NUMBER

CONTAINER_COLUMN NOT NULL VARCHAR2(30)

AGG_FUNCTION VARCHAR2(8)

DISTINCTFLAG VARCHAR2(1)

MEASURE LONG

SQL>

2.DBA_MVIEW_ANALYSIS 存放實體檢視的附加資訊

（結構比較長，略去）

3.DBA_MVIEW_DETAIL_RELATIONS 存放實體檢視的子查詢等資訊

SQL> desc dba_mview_detail_relations

名稱 空? 型別

----------------------------------------- -------- -------------

OWNER NOT NULL VARCHAR2(30)

MVIEW_NAME NOT NULL VARCHAR2(30)

DETAILOBJ_OWNER NOT NULL VARCHAR2(30)

DETAILOBJ_NAME NOT NULL VARCHAR2(30)

DETAILOBJ_TYPE VARCHAR2(9)

DETAILOBJ_ALIAS VARCHAR2(30)

SQL>

4.DBA_MVIEW_DETAIL_JOINS 存放實體檢視的列的連線關係資訊

5.DBA_MVIEW_DETAIL_KEYS 存放實體檢視的列或表示式的資訊

6.DBA_MVIEWS 存放實體檢視的基本資訊