Oracle 實體化視圖

[b]簡介：[/b]

MView中文名稱為實體化視圖（Materialized View），相對于普通的視圖來說，MView的不同之處在于MView實體化視圖管理存儲資料，占據資料庫的實體空間。MView的結果會儲存在一個普通的資料表中，在對MView進行查詢的時候不再會對建立MView的基表進行查詢，而是直接查詢MView對應的結果表，然後通過定期的重新整理機制來更新MView表中的資料。

物化視圖可以查詢表，視圖和其它的物化視圖。

[b]MView在實際中的主要作用[/b]：

1、減輕網絡負擔：通過MV将資料從一個資料庫分發到多個不同的資料庫上，通過對多個資料庫通路來減輕對單個資料庫的網絡負擔。

2、搭建分發環境：通過從一個中央資料庫将資料分發到多個節點資料庫，達到分發資料的目的。

3、複制資料子集：MV可以進行行級/列級的篩選，這樣可以複制需要的那一部分資料。

實體化視圖是用于彙總,預計算,複制或分發資料的對象, 在大型的資料庫中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查詢的速度。

建立實體化視圖管理的使用者的權限必須有: CREATE ANY MATERIALZED VIEW,CREATE TABLE,CREATE VIEW,SELECT等。

[b]建立實體化視圖：[/b]

建立物化視圖主要文法：

create materialized view [view_name]

{TABLESPACE (表空間名)} --儲存表空間

{BUILD [DEFERRED|IMMEDIATE(預設值)]} --延遲重新整理還是立即重新整理

refresh [fast|complete|force]

[

on [commit|demand] |

start with (start_time) next (next_time)

]

as

{建立物化視圖用的查詢語句}

相關參數說明：

1，

Build子句：定義建立方式，包括BUILD IMMEDIATE和BUILD DEFERRED兩種

BUILD IMMEDIATE 在建立實體化視圖的時候就生成資料，預設 BUILD IMMEDIATE

BUILD DEFERRED 在建立時不生成資料，以後根據需要在生成資料

2，

Refresh子句：定義資料同步重新整理，當基表發生了DML操作後，實體化視圖何時采用哪種方式和基表進行同步，文法：

[refresh [fast | complete | force]

[on demand | commit]

[start with date]

[next date]

[with {primary key | rowid}]

]

FAST 采用增量重新整理，隻重新整理自上次重新整理以後進行的修改,如果指定REFRESH FAST子句，那麼應該對主表建立物化視圖日志，如果沒有建立MView Log的話要進行快速重新整理的話會報錯

注意：對于增量重新整理選項，如果在子查詢中存在分析函數，則物化視圖不起作用。

COMPLETE 對整個物化視圖進行完全的重新整理

FORCE(預設) Oracle在重新整理時會去判斷是否可以進行快速重新整理，如果可以則采用Fast方式，否則采用Complete的方式，Force選項是預設選項

ON DEMAND(預設) 物化視圖在使用者需要的時候進行重新整理，可以手工通過DBMS_MVIEW.REFRESH等方法來進行重新整理，也可以通過JOB定時進行重新整理

ON COMMIT 實體化視圖在對基表的DML操作送出的同時進行重新整理

START WITH 第一次重新整理時間

NEXT 重新整理時間間隔

WITH PRIMARY KEY(預設) 生成主鍵實體化視圖,也就是說實體化視圖是基于表的主鍵，而不是ROWID(對應于ROWID子句)。 為了生成PRIMARY KEY子句，應該在表上定義主鍵，否則應該用基于ROWID的實體化視圖。主鍵實體化視圖允許識别實體化視圖表而不影響實體化視圖增量重新整理的可用性

WITH ROWID 隻有一個單一的主表，不能包括下面任何一項:

●Distinct

●聚合函數

●Group by

●子查詢

●連接配接

●SET操作

3，

Query Rewrite子句，定義查詢重寫

包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE兩種。分别指出建立的實體化視圖是否支援查詢重寫。查詢重寫是指當對實體化視圖的基表進行查詢時，Oracle會自動判斷能否通過查詢實體化視圖來得到結果，如果可以，則避免了聚集或連接配接操作，而直接從已經計算好的實體化視圖中讀取資料

ENABLE QUERY REWRITE 支援查詢重寫

DISABLE QUERY REWRITE 不支援查詢重寫，預設DISABLE QUERY REWRITE

注意：如果需要進行快速重新整理，則需要建立實體化視圖日志。實體化視圖日志根據不同實體化視圖的快速重新整理的需要，可以建立為ROWID或PRIMARY KEY類型的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的清單。

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

[b]建立多表關聯的實體化視圖：[/b]

實體化視圖包含多表關聯，但不包含聚合操作，僅包含表關聯的實體化視圖同樣支援在兩種情況下重新整理：

ON COMMIT 和 ON DEMAND。在 ON COMMIT 下，當基表發生DML操作并送出commit 的時候，實體化視圖被重新整理。

Oracle不允許實體化視圖中有self-jions 的存在。

假如指定重新整理方式為REFRESH FAST, oracle 執行附加的校驗，以确定fast refresh能被執行。這些附加的檢查包括：

1 每個基表下必須建立相應的 materialized view log

2 每個基表的rowid 必須在實體化視圖的select 語句中出現

3 如果存在外聯接，在where 子句不能有任何的selections.并且，所有的關聯必須以AND 相連，并且用“=”操作符。

4 如果存在外聯接，唯一性限制必須存在于外聯的 inner table 的相關列中。

[b]建立聚合類型的實體化視圖：[/b]

為了能夠進行快速重新整理(fast refersh)，在select 語句中必須包含所有在group by中出現的列名，并且必須有一個count(*)，所有在gorup by 中出現的列都必須有一個count(列名)出現在select 中。同樣，所有于實體化視圖相關的表上都必須建立實體化和視圖日志。有效的聚合函數包括：

SUM, COUNT(x),COUNT(*),AVG,VARIANCE,STDDEV,MIN,MAX被聚合的值可以是任何的

sql表達式。

[b]REFRESH FAST 總結：[/b]

一個包含關聯和聚合的實體化視圖是可能的。但有如下限制：

1,實體化視圖中不能包含非複用的表達式(具有實時特征的)。比如SYSDATE and ROWNUM.

2,實體化視圖中不能包含RAW or LONG 資料類型。

3,實體化視圖相關的表必須建立實體化視圖日志，并且滿足如下條件

包含被實體化視圖引用到的所有的列。指定with rowid和INCLUDING NEW VALUES.

如果表的預期操作是inserts/direct-loads, deletes, and updates的混合，指定SEQUENCE子句隻用SUM,COUNT,AVG,STDDEV,VARIANCE,MIN and MAX支援fast refresh.

對于每個聚合agg(expr),必須相應寫一個count(expr)與之對應.

如果VARIANCE(expr)or STDDEV(expr)被指定，那麼COUNT(expr)and SUM(expr) 必須被指定。在select清單中必須包含所有group by中出現的列.如果實體化視圖有如下的一點，那麼fast refresh 隻支援正常DML inserts和直接路徑的裝載(direct loads)

a 實體化視圖包含MIN 或者 MAX

b 實體化視圖包含SUM(expr)但是沒有COUNT(expr)

c 實體化視圖中不包含COUNT(*)

如上的實體化視圖稱之為insert-only materialized view.

包含MAX or MIN實體化視圖在delete或者混合的DML操作之後可以是 fast refresh，條件是視圖中沒有where 子句。

(10g以上适用)

如果沒有外聯接，在where 子句中可以包含有選擇性的條件和關聯

如果實體化視圖中包含外聯接，fast refresh隻支援在outer table被修改的情況。同樣，唯一性限制必須存在于inner join table中。如果有外聯，所有的關聯條件都必須用 AND 連接配接和必須用 = 操作符。