Oracle 物化視圖

一、物化視圖概述

Oracle的物化視圖是包括一個(gè)查詢結(jié)果的數(shù)據(jù)庫(kù)對(duì)像，它是遠(yuǎn)程數(shù)據(jù)的的本地副本，或者用來(lái)生成基于數(shù)據(jù)表求和的匯總表。物化視圖存儲(chǔ)基于遠(yuǎn)程表的數(shù)據(jù)，也可以稱(chēng)為快照。

物化視圖可以用于預(yù)先計(jì)算并保存表連接或聚集等耗時(shí)較多的操作的結(jié)果，這樣，在執(zhí)行查詢時(shí)，就可以避免進(jìn)行這些耗時(shí)的操作，而從快速的得到結(jié)果。物化視圖有很多方面和索引很相似：使用物化視圖的目的是為了提高查詢性能；物化視圖對(duì)應(yīng)用透明，增加和刪除物化視圖不會(huì)影響應(yīng)用程序中SQL語(yǔ)句的正確性和有效性；物化視圖需要占用存儲(chǔ)空間；當(dāng)基表發(fā)生變化時(shí)，物化視圖也應(yīng)當(dāng)刷新。

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

通常情況下，物化視圖被稱(chēng)為主表（在復(fù)制期間）或明細(xì)表（在數(shù)據(jù)倉(cāng)庫(kù)中）。

對(duì)于復(fù)制，物化視圖允許你在本地維護(hù)遠(yuǎn)程數(shù)據(jù)的副本,這些副本是只讀的。如果你想修改本地副本，必須用高級(jí)復(fù)制的功能。當(dāng)你想從一個(gè)表或視圖中抽取數(shù)據(jù)時(shí)，你可以用從物化視圖中抽取。

對(duì)于數(shù)據(jù)倉(cāng)庫(kù)，創(chuàng)建的物化視圖通常情況下是聚合視圖，單一表聚合視圖和連接視圖。

在復(fù)制環(huán)境下，創(chuàng)建的物化視圖通常情況下主鍵，rowid,和子查詢視圖。

物化視圖由于是物理真實(shí)存在的，故可以創(chuàng)建索引。

1.1 物化視圖可以分為以下三種類(lèi)型

• 包含聚集的物化視圖；

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

• 嵌套物化視圖。

三種物化視圖的快速刷新的限制條件有很大區(qū)別，而對(duì)于其他方面則區(qū)別不大。

創(chuàng)建物化視圖時(shí)可以指定多種選項(xiàng)，下面對(duì)幾種主要的選擇進(jìn)行簡(jiǎn)單說(shuō)明：

（1）創(chuàng)建方式（Build Methods）：包括BUILD IMMEDIATE和BUILD DEFERRED兩種。

• BUILD IMMEDIATE是在創(chuàng)建物化視圖的時(shí)候就生成數(shù)據(jù)。默認(rèn)為BUILD IMMEDIATE。

• BUILD DEFERRED則在創(chuàng)建時(shí)不生成數(shù)據(jù)，以后根據(jù)需要在生成數(shù)據(jù)。

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

分別指出創(chuàng)建的物化視圖是否支持查詢重寫(xiě)。查詢重寫(xiě)是指當(dāng)對(duì)物化視圖的基表進(jìn)行查詢時(shí)，oracle會(huì)自動(dòng)判斷能否通過(guò)查詢物化視圖來(lái)得到結(jié)果，如果可以，則避免了聚集或連接操作，而直接從已經(jīng)計(jì)算好的物化視圖中讀取數(shù)據(jù)。默認(rèn)為DISABLEQUERY REWRITE。

（3）刷新（Refresh）：指當(dāng)基表發(fā)生了DML操作后，物化視圖何時(shí)采用哪種方式和基表進(jìn)行同步。刷新的模式有兩種：ON DEMAND和ON COMMIT。

ON DEMAND和ON COMMIT物化視圖的區(qū)別在于其刷新方法的不同，ON DEMAND指物化視圖在用戶需要的時(shí)候進(jìn)行刷新，可以手工通過(guò)DBMS_MVIEW.REFRESH等方法來(lái)進(jìn)行刷新，也可以通過(guò)JOB定時(shí)進(jìn)行刷新,即更新物化視圖，以保證和基表數(shù)據(jù)的一致性;而ON COMMIT是說(shuō)，一旦基表有了COMMIT，即事務(wù)提交，則立刻刷新，立刻更新物化視圖，使得數(shù)據(jù)和基表一致。對(duì)基表，平常的COMMIT在0.01秒內(nèi)可以完成，但在有了ON COMMIT視圖后，居然要6秒。速度減低了很多倍。ON COMMIT視圖對(duì)基表的影響可見(jiàn)一斑。

1.2 物化視圖，根據(jù)不同的著重點(diǎn)可以有不同的分類(lèi):

1)按刷新方式分：FAST/COMPLETE/FORCE

2)按刷新時(shí)間的不同：ON DEMAND/ON COMMIT

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

4)按是否支持查詢重寫(xiě)：ENABLE QUERY REWRITE/DISABLEQUERY REWRITE

默認(rèn)情況下，如果沒(méi)指定刷新方法和刷新模式，則Oracle默認(rèn)為FORCE和DEMAND。

注意：設(shè)置REFRESH ON COMMIT的物化視圖不能訪問(wèn)遠(yuǎn)端對(duì)象。

在建立物化視圖的時(shí)候可以指定ORDER BY語(yǔ)句，使生成的數(shù)據(jù)按照一定的順序進(jìn)行保存。不過(guò)這個(gè)語(yǔ)句不會(huì)寫(xiě)入物化視圖的定義中，而且對(duì)以后的刷新也無(wú)效。

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

• 完全刷新（COMPLETE）會(huì)刪除表中所有的記錄（如果是單表刷新，可能會(huì)采用TRUNCATE的方式），然后根據(jù)物化視圖中查詢語(yǔ)句的定義重新生成物化視圖。

• 快速刷新（FAST）采用增量刷新的機(jī)制，只將自上次刷新以后對(duì)基表進(jìn)行的所有操作刷新到物化視圖中去。FAST必須創(chuàng)建基于主表的視圖日志。對(duì)于增量刷新選項(xiàng)，如果在子查詢中存在分析函數(shù)，則物化視圖不起作用。

• 采用FORCE方式，Oracle會(huì)自動(dòng)判斷是否滿足快速刷新的條件，如果滿足則進(jìn)行快速刷新，否則進(jìn)行完全刷新。

Oracle物化視圖的快速刷新機(jī)制是通過(guò)物化視圖日志完成的。Oracle通過(guò)一個(gè)物化視圖日志還可以支持多個(gè)物化視圖的快速刷新。

物化視圖日志根據(jù)不同物化視圖的快速刷新的需要，可以建立為ROWID或PRIMARY KEY類(lèi)型的。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

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

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

           [refresh [fast|complete|force]

           [on demand | commit]

           [start with date] [next date]

           [with {primary key|rowid}]]

1.4.1 主鍵和ROWD子句：

WITH PRIMARY KEY選項(xiàng)生成主鍵物化視圖,也就是說(shuō)物化視圖是基于主表的主鍵，而不是ROWID(對(duì)應(yīng)于ROWID子句). PRIMARY KEY是默認(rèn)選項(xiàng),為了生成PRIMARY KEY子句，應(yīng)該在主表上定義主鍵，否則應(yīng)該用基于ROWID的物化視圖.

基于ROWID物化視圖只有一個(gè)單一的主表，不能包括下面任何一項(xiàng):

（1）Distinct 或者聚合函數(shù).

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

1.4.2 刷新時(shí)間:

START WITH子句通知數(shù)據(jù)庫(kù)完成從主表到本地表第一次復(fù)制的時(shí)間,應(yīng)該及時(shí)估計(jì)下一次運(yùn)行的時(shí)間點(diǎn), NEXT 子句說(shuō)明了刷新的間隔時(shí)間.

1.5 ON PREBUILD TABLE 說(shuō)明

在創(chuàng)建物化視圖時(shí)指明ON PREBUILD TABLE語(yǔ)句，可以將物化視圖建立在一個(gè)已經(jīng)存在的表上。這種情況下，物化視圖和表必須同名。當(dāng)刪除物化視圖時(shí)，不會(huì)刪除同名的表。

這種物化視圖的查詢重寫(xiě)要求參數(shù)QUERY_REWRITE_INTEGERITY必須設(shè)置為trusted或者stale_tolerated。

1.6 物化視圖分區(qū)

物化視圖可以進(jìn)行分區(qū)。而且基于分區(qū)的物化視圖可以支持分區(qū)變化跟蹤（PCT）。具有這種特性的物化視圖，當(dāng)基表進(jìn)行了分區(qū)維護(hù)操作后，仍然可以進(jìn)行快速刷新操作。對(duì)于聚集物化視圖，可以在GROUP BY列表中使用CUBE或ROLLUP，來(lái)建立不同等級(jí)的聚集物化視圖。

二、物化視圖使用示例

2.1 創(chuàng)建物化視圖

create materialized view mv_emp_pk 
refresh fast         --快速刷新
build immediate      --立即刷新
on demand            --按照指定方式刷新
start with sysdate   --第一次刷新時(shí)間，sysdate表示當(dāng)前時(shí)間，也可以使用to_date()指定時(shí)間
next sysdate+1       --刷新時(shí)間間隔
with primary key     --創(chuàng)建主鍵物化視圖，也可以使用with rowid
as                   --子查詢
select employee_id 
from employees 
where department_id=10;

可以看到報(bào)錯(cuò)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.

從錯(cuò)誤描述上可以知道，要?jiǎng)?chuàng)建的物化視圖的刷新方式為FAST,但是表employees上沒(méi)有創(chuàng)建物化視圖日志，所以報(bào)上面的錯(cuò)誤。

在表employees上創(chuàng)建物化視圖日志后再創(chuàng)建物化視圖。

zx@ORA11G>create materialized view log on employees;

Materialized view log created.

zx@ORA11G>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.

zx@ORA11G>select * from mv_emp_pk;

EMPLOYEE_ID
-----------
        200
        
zx@ORA11G>select employee_id from employees where department_id=10;

EMPLOYEE_ID
-----------
        200

2.2 查看job中物化視圖刷新的時(shí)間

zx@ORA11G>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表中的數(shù)據(jù)

zx@ORA11G>insert into employees (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID) values(209,'zx','zx@163.com',sysdate,10,10);

1 row created.

zx@ORA11G>commit;

Commit complete.

zx@ORA11G>select * from mv_emp_pk;

EMPLOYEE_ID
-----------
	200

新插入的數(shù)據(jù)沒(méi)有刷新到物化視圖中

2.4 刷新物化視圖

（1）使用dbms_mview.refresh 手工刷新

zx@ORA11G>exec dbms_mview.refresh('MV_EMP_PK');

PL/SQL procedure successfully completed.

zx@ORA11G>select * from mv_emp_pk;

EMPLOYEE_ID
-----------
	200
	209

zx@ORA11G>exec dbms_mview.refresh('MV_EMP_PK','c');--全量刷新

PL/SQL procedure successfully completed.

zx@ORA11G>exec dbms_mview.refresh('MV_EMP_PK','f');--快速刷新

PL/SQL procedure successfully completed.

（2）使用dbms_refresh.refresh 過(guò)程來(lái)批量刷新MV

 如果我們?cè)趧?chuàng)建物化視圖的過(guò)程指定start 和next time的刷新時(shí)間，那么Oracle 會(huì)自動(dòng)創(chuàng)建刷新的job，并采用dbms_refresh.refresh 的方式。如2.2所示

如果沒(méi)有指定next time使用這種方式刷新之前需要先make refresh group，然后才可以刷新。

假設(shè)存在物化視圖MV_T1, MV_T2, MV_T3. 創(chuàng)建refresh group的語(yǔ)法如下：

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

2.5 查詢物化視圖狀態(tài)

--user_mviews視圖
zx@ORA11G>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視圖
zx@ORA11G>col owner for a10
zx@ORA11G>col name for a10
zx@ORA11G>col master_owner for a10
zx@ORA11G>col master for a10
zx@ORA11G>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 刪除物化視圖和日志

zx@ORA11G>drop materialized view mv_emp_pk;

Materialized view dropped.

zx@ORA11G>drop materialized view log on employees;

Materialized view log dropped.

如果刪除物化視圖日志后，再以fash方式刷新物化視圖會(huì)報(bào)如下錯(cuò)誤

zx@ORA11G>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 log
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at line 1

2.7 創(chuàng)建遠(yuǎn)程物化視圖

--在遠(yuǎn)端創(chuàng)建一個(gè)物化視圖
zx@ORA12C>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@link_ora11g 
 11  where department_id=10;

Materialized view created.

zx@ORA12C>select * from mv_emp_pk;

EMPLOYEE_ID
-----------
        200
        209
--在源端刪除employees表中的數(shù)據(jù)
zx@ORA11G>delete from employees where employee_id=209;

1 row deleted.

zx@ORA11G>commit;

Commit complete.
        
zx@ORA12C>select * from mv_emp_pk;

EMPLOYEE_ID
-----------
        200
        209
--刷新物化視圖
zx@ORA12C>exec dbms_mview.refresh('MV_EMP_PK');

PL/SQL procedure successfully completed.

zx@ORA12C>select * from mv_emp_pk;

EMPLOYEE_ID
-----------
        200

2.8 創(chuàng)建基于commit的物化視圖

zx@ORA11G>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.

zx@ORA11G>select * from mv_emp_commit;

EMPLOYEE_ID
-----------
        200
--插入新數(shù)據(jù)并提交
zx@ORA11G>insert into employees (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID,DEPARTMENT_ID) values(209,'zx','zx@163.com',sysdate,10,10);

1 row created.

zx@ORA11G>commit;

Commit complete.
--commit后物化視圖自動(dòng)刷新
zx@ORA11G>select * from mv_emp_commit;

EMPLOYEE_ID
-----------
        200
        209

zx@ORA11G>delete from employees where employee_id=209;

1 row deleted.

zx@ORA11G>select * from mv_emp_commit;

EMPLOYEE_ID
-----------
        200
        209

zx@ORA11G>commit;

Commit complete.

zx@ORA11G>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