Oracle 物化視圖

標籤：oracle materialized view

一、物化視圖概述

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

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

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

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

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

對於資料倉儲，建立的物化視圖通常情況下是彙總檢視，單一表彙總檢視和串連視圖。

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

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

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

• 包含聚集的物化視圖；

• 只包含串連的物化視圖；

• 嵌套物化視圖。

三種物化視圖的快速重新整理的限制條件有很大區別，而對於其他方面則區別不大。

建立物化視圖時可以指定多種選項，下面對幾種主要的選擇進行簡單說明：

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

• BUILD IMMEDIATE是在建立物化視圖的時候就產生資料。預設為BUILD IMMEDIATE。

• BUILD DEFERRED則在建立時不產生資料，以後根據需要在產生資料。

（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。

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

• 快速重新整理（FAST）採用增量重新整理的機制，只將自上次重新整理以後對基表進行的所有操作重新整理到物化視圖中去。FAST必須建立基於主表的視圖日誌。對於增量重新整理選項，如果在子查詢中存在分析函數，則物化視圖不起作用。

• 採用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操作

1.4.2 重新整理時間:

START WITH子句通知數據庫完成從主表到本地表第一次複製的時間,應該及時估計下一次啟動並執行時間點, NEXT 子句說明了重新整理的間隔時間.

1.5 ON PREBUILD TABLE 說明

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

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

1.6 物化視圖分區

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

二、物化視圖使用樣本

2.1 建立物化視圖

create materialized view mv_emp_pk refresh fast         --快速重新整理build immediate      --立即重新整理on demand            --按照指定方式重新整理start with sysdate   --第一次重新整理時間，sysdate表示目前時間，也可以使用to_date()指定時間next sysdate+1       --重新整理時間間隔with primary key     --建立主鍵物化視圖，也可以使用with rowidas                   --子查詢select employee_id from employees where department_id=10;

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M02/9E/0A/wKioL1mKiC7gnc8iAAAs9AK60js707.png" title="1.png" alt="wKioL1mKiC7gnc8iAAAs9AK60js707.png" />

可以看到報錯ORA-23413

23413, 00000, "table \"%s\".\"%s\" does not have a materialized view log"

// *Cause: The fast refresh can not be performed because the master table

//         does not contain a materialized view log.

// *Action: Use the CREATE MATERIALIZED VIEW LOG command to create a 

//          materialized view log on the master table.

從錯誤描述上可以知道，要建立的物化視圖的重新整理方式為FAST,但是表employees上沒有建立物化視圖日誌，所以報上面的錯誤。

在表employees上建立物化視圖日誌後再建立物化視圖。

[email protected]>create materialized view log on employees;Materialized view log created.[email protected]>create materialized view mv_emp_pk   2  build immediate   3  refresh fast                4  on demand              5  start with sysdate     6  next sysdate+1         7  with primary key       8  as                    9  select employee_id  10  from employees  11  where department_id=10;Materialized view created.[email protected]>select * from mv_emp_pk;EMPLOYEE_ID-----------        200        [email protected]>select employee_id from employees where department_id=10;EMPLOYEE_ID-----------        200

2.2 查看job中物化視圖重新整理的時間

[email protected]>select job,what,last_date,next_date from dba_jobs where log_user=‘ZX‘;       JOB WHAT       LAST_DATE NEXT_DATE---------- -------------------------------------------------- ----------------- ----------------- 3 dbms_refresh.refresh(‘"ZX"."MV_EMP_PK"‘);      20170809 12:02:41 20170810 12:02:41

2.3 修改employees表中的資料

[email protected]>insert into employees (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID) values(209,‘zx‘,‘[email protected]‘,sysdate,10,10);1 row created.[email protected]>commit;Commit complete.[email protected]>select * from mv_emp_pk;EMPLOYEE_ID-----------200

新插入的資料沒有重新整理到物化視圖中

2.4 重新整理物化視圖

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

[email protected]>exec dbms_mview.refresh(‘MV_EMP_PK‘);PL/SQL procedure successfully completed.[email protected]>select * from mv_emp_pk;EMPLOYEE_ID-----------200209[email protected]>exec dbms_mview.refresh(‘MV_EMP_PK‘,‘c‘);--全量重新整理PL/SQL procedure successfully completed.[email protected]>exec dbms_mview.refresh(‘MV_EMP_PK‘,‘f‘);--快速重新整理PL/SQL procedure successfully completed.

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

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

如果沒有指定next time使用這種方式重新整理之前需要先make refresh group，然後才可以重新整理。

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

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‘) ;

2.5 查詢物化檢視狀態

--user_mviews視圖[email protected]>select owner,mview_name,refresh_mode,LAST_REFRESH_TYPE,LAST_REFRESH_DATE,REFRESH_MODE from user_mviews;OWNER       MVIEW_NAME      REFRES LAST_REF LAST_REFRESH_DATE REFRES------------------------------ ------------------------------ ------ -------- ----------------- ------ZX       MV_EMP_PK      DEMAND FAST     20170809 12:26:40 DEMAND--user_mview_refresh_times視圖[email protected]>col owner for a10[email protected]>col name for a10[email protected]>col master_owner for a10[email protected]>col master for a10[email protected]>select * from user_mview_refresh_times;OWNER   NAME       MASTER_OWN MASTER     LAST_REFRESH---------- ---------- ---------- ---------- -----------------ZX   MV_EMP_PK  ZX EMPLOYEES  20170809 12:26:40

2.6 刪除物化視圖和日誌

[email protected]>drop materialized view mv_emp_pk;Materialized view dropped.[email protected]>drop materialized view log on employees;Materialized view log dropped.

如果刪除物化視圖日誌後，再以fash方式重新整理物化視圖會報如下錯誤

[email protected]>exec dbms_mview.refresh(‘MV_EMP_PK‘,‘f‘);BEGIN dbms_mview.refresh(‘MV_EMP_PK‘,‘f‘); END;*ERROR at line 1:ORA-23413: table "ZX"."EMPLOYEES" does not have a materialized view logORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994ORA-06512: at line 1

2.7 建立遠程物化視圖

--在遠端建立一個物化視圖[email protected]>create materialized view mv_emp_pk   2  build immediate   3  refresh fast                4  on demand              5  start with sysdate     6  next sysdate+1         7  with primary key       8  as                    9  select employee_id  10  from [email protected]_ora11g  11  where department_id=10;Materialized view created.[email protected]>select * from mv_emp_pk;EMPLOYEE_ID-----------        200        209--在源端刪除employees表中的資料[email protected]>delete from employees where employee_id=209;1 row deleted.[email protected]>commit;Commit complete.        [email protected]>select * from mv_emp_pk;EMPLOYEE_ID-----------        200        209--重新整理物化視圖[email protected]>exec dbms_mview.refresh(‘MV_EMP_PK‘);PL/SQL procedure successfully completed.[email protected]>select * from mv_emp_pk;EMPLOYEE_ID-----------        200

2.8 建立基於commit的物化視圖

[email protected]>create materialized view mv_emp_commit  2  refresh fast   3  on commit  4  with primary key  5  as   6  select employee_id   7  from employees   8  where department_id=10;Materialized view created.[email protected]>select * from mv_emp_commit;EMPLOYEE_ID-----------        200--插入新資料並提交[email protected]>insert into employees (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID) values(209,‘zx‘,‘[email protected]‘,sysdate,10,10);1 row created.[email protected]>commit;Commit complete.--commit後物化視圖自動重新整理[email protected]>select * from mv_emp_commit;EMPLOYEE_ID-----------        200        209[email protected]>delete from employees where employee_id=209;1 row deleted.[email protected]>select * from mv_emp_commit;EMPLOYEE_ID-----------        200        209[email protected]>commit;Commit complete.[email protected]>select * from mv_emp_commit;EMPLOYEE_ID-----------        200

參考：http://blog.csdn.net/tianlesoftware/article/details/4713553

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6002.htm#SQLRF01302

http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT411

本文出自 “DBA Fighting!” 部落格，請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1954739

Oracle 物化視圖