物化視圖的定義,建立,重新整理,删除等

一．物化視圖概述

Oracle的物化視圖是包括一個查詢結果的資料庫對像，它是遠端資料的的本地副本，或者用來生成基于資料表求和的彙總表。物化視圖存儲基于遠端表的資料，也可以稱為快照。

物化視圖可以用于預先計算并儲存表連接配接或聚集等耗時較多的操作的結果，這樣，在執行查詢時，就可以避免進行這些耗時的操作，而從快速的得到結果。物化視圖有很多方面和索引很相似：使用物化視圖的目的是為了提高查詢性能；物化視圖對應用透明，增加和删除物化視圖不會影響應用程式中SQL語句的正确性和有效性；物化視圖需要占用存儲空間；當基表發生變化時，物化視圖也應當重新整理。

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

通常情況下，物化視圖被稱為主表（在複制期間）或明細表（在資料倉庫中）。

對于複制，物化視圖允許你在本地維護遠端資料的副本,這些副本是隻讀的。如果你想修改本地副本，必須用進階複制的功能。當你想從一個表或視圖中抽取資料時，你可以用從物化視圖中抽取。

對于資料倉庫，建立的物化視圖通常情況下是聚合視圖，單一表聚合視圖和連接配接視圖。

在複制環境下，建立的物化視圖通常情況下主鍵，rowid,和子查詢視圖。

物化視圖由于是實體真實存在的，故可以建立索引。

1.1 物化視圖可以分為以下三種類型

（1）包含聚集的物化視圖；

（2）隻包含連接配接的物化視圖；

（3）嵌套物化視圖。

三種物化視圖的快速重新整理的限制條件有很大差別，而對于其他方面則差別不大。建立物化視圖時可以指定多種選項，下面對幾種主要的選擇進行簡單說明：

（1）建立方式（BuildMethods）：包括BUILD IMMEDIATE和BUILD DEFERRED兩種。

BUILD IMMEDIATE是在建立物化視圖的時候就生成資料。

BUILD DEFERRED則在建立時不生成資料，以後根據需要在生成資料。預設為BUILD IMMEDIATE。

（2）查詢重寫（QueryRewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE兩種。

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

（3）重新整理（Refresh）：指當基表發生了DML操作後，物化視圖何時采用哪種方式和基表進行同步。重新整理的模式有兩種：ON DEMAND和ON COMMIT。

ON DEMAND和ON COMMIT物化視圖的差別在于其重新整理方法的不同，ON DEMAND指物化視圖在使用者需要的時候進行重新整理，可以手工通過DBMS_MVIEW.REFRESH等方法來進行重新整理，也可以通過JOB定時進行重新整理,即更新物化視圖，以保證和基表資料的一緻性;而ON COMMIT是說，一旦基表有了COMMIT，即事務送出，則立刻重新整理，立刻更新物化視圖，使得資料和基表一緻。

對基表，平常的COMMIT在0.01秒内可以完成，但在有了ON COMMIT視圖後，居然要6秒。速度減低了很多倍。ON COMMIT視圖對基表的影響可見一斑。

1.2 物化視圖，根據不同的着重點可以有不同的分類:

1) 按重新整理方式分：FAST/COMPLETE/FORCE

2) 按重新整理時間的不同：ON DEMAND/ON COMMIT

3) 按是否可更新：UPDATABLE/READ ONLY

4) 按是否支援查詢重寫：ENABLE QUERY REWRITE/DISABLEQUERY REWRITE

預設情況下，如果沒指定重新整理方法和重新整理模式，則Oracle預設為FORCE和DEMAND。

注意：設定REFRESH ON COMMIT的物化視圖不能通路遠端對象。

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

1.3 物化視圖有三種重新整理方式：COMPLETE、FAST和FORCE。

1) 完全重新整理（COMPLETE）會删除表中所有的記錄（如果是單表重新整理，可能會采用TRUNCATE的方式），然後根據物化視圖中查詢語句的定義重新生成物化視圖。

2) 快速重新整理（FAST）采用增量重新整理的機制，隻将自上次重新整理以後對基表進行的所有操作重新整理到物化視圖中去。FAST必須建立基于主表的視圖日志。

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

3) 采用FORCE方式，Oracle會自動判斷是否滿足快速重新整理的條件，如果滿足則進行快速重新整理，否則進行完全重新整理。

Oracle物化視圖的快速重新整理機制是通過物化視圖日志完成的。Oracle通過一個物化視圖日志還可以支援多個物化視圖的快速重新整理。

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

1.4 物化視圖Refresh子句的其他說明與示例

REFRESH 子句可以包含如下部分：

[refresh [fast|complete|force]

[on demand | commit]

[start with date] [next date]

[with {primary key|rowid}]]

1.4.1 主鍵和ROWD子句：

WITH PRIMARY KEY選項生成主鍵物化視圖,也就是說物化視圖是基于主表的主鍵，而不是ROWID(對應于ROWID子句). PRIMARY KEY是預設選項,為了生成PRIMARY KEY子句，應該在主表上定義主鍵，否則應該用基于ROWID的物化視圖.

基于ROWID物化視圖隻有一個單一的主表，不能包括下面任何一項:

（1）.Distinct 或者聚合函數.

（2） .Group by，子查詢，連接配接和SET操作

--主鍵（PrimaryKey）物化視圖示例：

在遠端資料庫表emp上建立主鍵物化視圖：

[sql] view plain copy

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFASTSTARTWITHSYSDATE
NEXT SYSDATE + 1/48
WITHPRIMARYKEY
ASSELECT * FROM [email protected]_db

[sql] view plain copy

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFASTSTARTWITHSYSDATE
NEXT SYSDATE + 1/48
WITHPRIMARYKEY
ASSELECT * FROM [email protected]_db

--當用FAST選項建立物化視圖，必須建立基于主表的視圖日志,如下:

[sql] view plain copy

CREATEMATERIALIZEDVIEWLOGON emp;

[sql] view plain copy

CREATEMATERIALIZEDVIEWLOGON emp;

--Rowid物化視圖示例：

下面的文法在遠端資料庫表emp上建立Rowid物化視圖

[sql] view plain copy

CREATEMATERIALIZEDVIEW mv_emp_rowid
REFRESHWITHROWID
ASSELECT * FROM [email protected]_db;
Materializedviewlog created.

[sql] view plain copy

CREATEMATERIALIZEDVIEW mv_emp_rowid
REFRESHWITHROWID
ASSELECT * FROM [email protected]_db;
Materializedviewlog created.

--子查詢物化視圖示例：

在遠端資料庫表emp上建立基于emp和dept表的子查詢物化視圖

[sql] view plain copy

CREATEMATERIALIZEDVIEW mv_empdept
ASSELECT * FROM [email protected]_db e
WHEREEXISTS
(SELECT * FROM [email protected]_db d
WHEREe.dept_no = d.dept_no)

[sql] view plain copy

CREATEMATERIALIZEDVIEW mv_empdept
ASSELECT * FROM [email protected]_db e
WHEREEXISTS
(SELECT * FROM [email protected]_db d
WHEREe.dept_no = d.dept_no)

1.4.2 重新整理時間

START WITH子句通知資料庫完成從主表到本地表第一次複制的時間,應該及時估計下一次運作的時間點, NEXT 子句說明了重新整理的間隔時間.

[sql] view plain copy

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFAST
STARTWITHSYSDATE
NEXT SYSDATE + 2
WITHPRIMARYKEY
ASSELECT * FROM [email protected]_db;

[sql] view plain copy

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFAST
STARTWITHSYSDATE
NEXT SYSDATE + 2
WITHPRIMARYKEY
ASSELECT * FROM [email protected]_db;

在上面的例子中，物化視圖資料的第一個副本在建立時生成，以後每兩天重新整理一次.

[sql] view plain copy

creatematerializedviewMV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA --儲存表空間
BUILDDEFERRED--延遲重新整理不立即重新整理
refreshforce--如果可以快速重新整理則進行快速重新整理，否則完全重新整理
ondemand--按照指定方式重新整理
startwith to_date('24-11-200518:00:10', 'dd-mm-yyyyhh24:mi:ss') --第一次重新整理時間
nextTRUNC(SYSDATE+1)+18/24--重新整理時間間隔
as
SELECT * FROM [email protected]_db;

[sql] view plain copy

creatematerializedviewMV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA --儲存表空間
BUILDDEFERRED--延遲重新整理不立即重新整理
refreshforce--如果可以快速重新整理則進行快速重新整理，否則完全重新整理
ondemand--按照指定方式重新整理
startwith to_date('24-11-200518:00:10', 'dd-mm-yyyyhh24:mi:ss') --第一次重新整理時間
nextTRUNC(SYSDATE+1)+18/24--重新整理時間間隔
as
SELECT * FROM [email protected]_db;

1.5 ON PREBUILD TABLE 說明

在建立物化視圖時指明ON PREBUILD TABLE語句，可以将物化視圖建立在一個已經存在的表上。這種情況下，物化視圖和表必須同名。當删除物化視圖時，不會删除同名的表。

這種物化視圖的查詢重寫要求參數QUERY_REWRITE_INTEGERITY必須設定為trusted或者stale_tolerated。

1.6 物化視圖分區

物化視圖可以進行分區。而且基于分區的物化視圖可以支援分區變化跟蹤（PCT）。具有這種特性的物化視圖，當基表進行了分區維護操作後，仍然可以進行快速重新整理操作。對于聚集物化視圖，可以在GROUP BY清單中使用CUBE或ROLLUP，來建立不同等級的聚集物化視圖。

相關内容參考：

Oracle 物化視圖日志與快速重新整理說明

http://blog.csdn.net/tianlesoftware/article/details/7720580

Oracle 物化視圖詳細錯誤描述檢視方法

http://blog.csdn.net/tianlesoftware/article/details/7719789

Oracle 物化視圖快速重新整理限制說明

http://blog.csdn.net/tianlesoftware/article/details/7719679

二．物化視圖操作示例

1. 建立物化視圖需要的權限：

[sql] view plain copy

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

[sql] view plain copy

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

2. 在源表建立物化視圖日志

[sql] view plain copy

CREATE MATERIALIZED VIEW LOG ON DAVE
TABLESPACE&BISONCU_SPACE -- 日志空間
WITH PRIMARY KEY; -- 指定為主鍵類型

[sql] view plain copy

CREATE MATERIALIZED VIEW LOG ON DAVE
TABLESPACE&BISONCU_SPACE -- 日志空間
WITH PRIMARY KEY; -- 指定為主鍵類型

3. 授權給中間使用者

[sql] view plain copy

GRANT SELECT ON DAVE TO ANQING;
GRANT SELECT ON MLOG$_DAVE TO ANQING;

[sql] view plain copy

GRANT SELECT ON DAVE TO ANQING;
GRANT SELECT ON MLOG$_DAVE TO ANQING;

4. 在目标資料庫上建立MATERIALIZED VIEW

[sql] view plain copy

CREATE MATERIALIZED VIEW AICS_DAVE
TABLESPACE&BISONCS_SPACE
REFRESH FAST
ON DEMAND
--第一次重新整理時間
--START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')
START WITH SYSDATE
--重新整理時間間隔。每1天重新整理一次，時間為淩晨2點
--NEXT TRUNC(SYSDATE,'dd')+1+2/24
NEXT SYSDATE+1/24/20
WITH PRIMARY KEY
--USING DEFAULT LOCAL ROLLBACKSEGMENT
DISABLE QUERY REWRITE AS
SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION
FROM [email protected]_DAVE;

[sql] view plain copy

CREATE MATERIALIZED VIEW AICS_DAVE
TABLESPACE&BISONCS_SPACE
REFRESH FAST
ON DEMAND
--第一次重新整理時間
--START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')
START WITH SYSDATE
--重新整理時間間隔。每1天重新整理一次，時間為淩晨2點
--NEXT TRUNC(SYSDATE,'dd')+1+2/24
NEXT SYSDATE+1/24/20
WITH PRIMARY KEY
--USING DEFAULT LOCAL ROLLBACKSEGMENT
DISABLE QUERY REWRITE AS
SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION
FROM [email protected]_DAVE;

5. 在目标物化視圖上建立索引

[sql] view plain copy

CREATE INDEX IDX_T_DV_CT
ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME)
TABLESPACE &BISON_IDX;
CREATE INDEX IDX_T_DV_UT
ON AICS_DEV_INFO (UPDATE_TIME)
TABLESPACE &BISON_IDX;
CREATE INDEX I_T_DV_MSISDN
ON AICS_DEV_INFO (MSISDN)
TABLESPACE &BISON_IDX;

[sql] view plain copy

CREATE INDEX IDX_T_DV_CT
ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME)
TABLESPACE &BISON_IDX;
CREATE INDEX IDX_T_DV_UT
ON AICS_DEV_INFO (UPDATE_TIME)
TABLESPACE &BISON_IDX;
CREATE INDEX I_T_DV_MSISDN
ON AICS_DEV_INFO (MSISDN)
TABLESPACE &BISON_IDX;

6. 物化視圖重新整理說明

（1）使用dbms_mview.refresh 手工重新整理

如：

[sql] view plain copy

EXEC DBMS_MVIEW.REFRESH('MV_DAVE');
--完全重新整理
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');
--快速重新整理
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F');

[sql] view plain copy

EXEC DBMS_MVIEW.REFRESH('MV_DAVE');
--完全重新整理
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');
--快速重新整理
EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f');
EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F');

（2）使用dbms_refresh.refresh 過程來批量重新整理MV

如果我們在建立物化視圖的過程指定start 和next time的重新整理時間，那麼Oracle 會自動建立重新整理的job，并采用dbms_refresh.refresh 的方式。

使用這種方式重新整理之前需要先make refresh group，然後才可以重新整理。

Refreshmake 的文法可以參考：

http://docs.oracle.com/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#i94057

示例：

假設存在物化視圖MV_T1, MV_T2, MV_T3. 建立refresh group的文法如下：

[sql] view plain copy

SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1')
--重新整理整個refresh group 組：
SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')

[sql] view plain copy

SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1')
--重新整理整個refresh group 組：
SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')

7. 删除物化視圖及日志

[sql] view plain copy

--删除物化視圖日志：
DROP MATERIALIZED VIEW LOG ON DAVE;
--删除物化視圖
DROP MATERIALIZED VIEW MV_DAVE;

[sql] view plain copy

--删除物化視圖日志：
DROP MATERIALIZED VIEW LOG ON DAVE;
--删除物化視圖
DROP MATERIALIZED VIEW MV_DAVE;

8. 檢視物化視圖重新整理狀态資訊

[sql] view plain copy

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROMUSER_MVIEWS;
SQL> SELECT NAME, LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES;

[sql] view plain copy

SQL> SELECT MVIEW_NAME, LAST_REFRESH_DATE, STALENESS FROMUSER_MVIEWS;
SQL> SELECT NAME, LAST_REFRESH FROM USER_MVIEW_REFRESH_TIMES;

9. 查詢物化視圖日志：

[sql] view plain copy

SELECT * FROM MLOG$_DAVE;

物化視圖的定義,建立,重新整理,删除等

一．物化視圖概述

1.1 物化視圖可以分為以下三種類型

1.2 物化視圖，根據不同的着重點可以有不同的分類:

1.3 物化視圖有三種重新整理方式：COMPLETE、FAST和FORCE。

1.4 物化視圖Refresh子句的其他說明與示例

1.4.1 主鍵和ROWD子句：

1.4.2 重新整理時間

1.5 ON PREBUILD TABLE 說明

1.6 物化視圖分區

二．物化視圖操作示例

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List

物化視圖的定義,建立,重新整理,删除等

一． 物化視圖概述

1.1 物化視圖可以分為以下三種類型

1.2 物化視圖，根據不同的着重點可以有不同的分類:

1.3 物化視圖有三種重新整理方式：COMPLETE、FAST和FORCE。

1.4 物化視圖Refresh子句的其他說明與示例

1.4.1 主鍵和ROWD子句：

1.4.2 重新整理時間

1.5 ON PREBUILD TABLE 說明

1.6 物化視圖分區

二． 物化視圖操作示例

繼續閱讀

一．物化視圖概述

二．物化視圖操作示例