The important thing in life is to have a great aim , and the determination

Oracle 物化視圖 說明

一．    物化視圖概述

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

 

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

 

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

 

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

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

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

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

 

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

 

 

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

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

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

（3）    嵌套物化視圖。

 

三種物化視圖的快速刷新的限制條件有很大區(qū)別，而對(duì)于其他方面則區(qū)別不大。創(chuàng)建物化視圖時(shí)可以指定多種選項(xiàng)，下面對(duì)幾種主要的選擇進(jìn)行簡單說明：

 

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

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

BUILD DEFERRED則在創(chuàng)建時(shí)不生成數(shù)據(jù)，以后根據(jù)需要在生成數(shù)據(jù)。默認(rèn)為BUILD IMMEDIATE。

 

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

分別指出創(chuàng)建的物化視圖是否支持查詢重寫。查詢重寫是指當(dāng)對(duì)物化視圖的基表進(jìn)行查詢時(shí)，Oracle會(huì)自動(dòng)判斷能否通過查詢物化視圖來得到結(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)行刷新，可以手工通過DBMS_MVIEW.REFRESH等方法來進(jìn)行刷新，也可以通過JOB定時(shí)進(jìn)行刷新,即更新物化視圖，以保證和基表數(shù)據(jù)的一致性;而ON COMMIT是說，一旦基表有了COMMIT，即事務(wù)提交，則立刻刷新，立刻更新物化視圖，使得數(shù)據(jù)和基表一致。

對(duì)基表，平常的COMMIT在0.01秒內(nèi)可以完成，但在有了ON COMMIT視圖后，居然要6秒。速度減低了很多倍。ON COMMIT視圖對(duì)基表的影響可見一斑。

 

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

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

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

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

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

 

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

 

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

 

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

 

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

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

2)        快速刷新（FAST）采用增量刷新的機(jī)制，只將自上次刷新以后對(duì)基表進(jìn)行的所有操作刷新到物化視圖中去。FAST必須創(chuàng)建基于主表的視圖日志。

            對(duì)于增量刷新選項(xiàng)，如果在子查詢中存在分析函數(shù)，則物化視圖不起作用。

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

 

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

物化視圖日志根據(jù)不同物化視圖的快速刷新的需要，可以建立為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選項(xiàng)生成主鍵物化視圖,也就是說物化視圖是基于主表的主鍵，而不是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操作

 

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

在遠(yuǎn)程數(shù)據(jù)庫表emp上創(chuàng)建主鍵物化視圖：

 

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFASTSTARTWITHSYSDATE
NEXT  SYSDATE + 1/48
WITHPRIMARYKEY
ASSELECT * FROM emp@remote_db

 

 

--當(dāng)用FAST選項(xiàng)創(chuàng)建物化視圖，必須創(chuàng)建基于主表的視圖日志,如下:

CREATEMATERIALIZEDVIEWLOGON emp;

 

--Rowid物化視圖示例：

下面的語法在遠(yuǎn)程數(shù)據(jù)庫表emp上創(chuàng)建Rowid物化視圖

CREATEMATERIALIZEDVIEW mv_emp_rowid
REFRESHWITHROWID
ASSELECT * FROM emp@remote_db;
Materializedviewlog created.

 

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

在遠(yuǎn)程數(shù)據(jù)庫表emp上創(chuàng)建基于emp和dept表的子查詢物化視圖

CREATEMATERIALIZEDVIEW  mv_empdept
ASSELECT * FROM emp@remote_db e
WHEREEXISTS
(SELECT * FROM dept@remote_db d
WHEREe.dept_no = d.dept_no)

 

 

1.4.2 刷新時(shí)間

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

 

CREATEMATERIALIZEDVIEW mv_emp_pk
REFRESHFAST
STARTWITHSYSDATE
NEXT  SYSDATE + 2
WITHPRIMARYKEY
ASSELECT * FROM emp@remote_db;

 

 

在上面的例子中，物化視圖數(shù)據(jù)的第一個(gè)副本在創(chuàng)建時(shí)生成，以后每兩天刷新一次.

 

creatematerializedviewMV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA --保存表空間
BUILDDEFERRED--延遲刷新不立即刷新
refreshforce--如果可以快速刷新則進(jìn)行快速刷新，否則完全刷新
ondemand--按照指定方式刷新
startwith to_date('24-11-200518:00:10', 'dd-mm-yyyyhh24:mi:ss') --第一次刷新時(shí)間
nextTRUNC(SYSDATE+1)+18/24--刷新時(shí)間間隔
as
SELECT * FROM emp@remote_db;

 

1.5 ON PREBUILD TABLE 說明

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

這種物化視圖的查詢重寫要求參數(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，來建立不同等級(jí)的聚集物化視圖。

 

 

相關(guān)內(nèi)容參考：

Oracle 物化視圖日志 與 快速刷新 說明

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

 

Oracle 物化視圖 詳細(xì)錯(cuò)誤描述 查看方法

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

 

Oracle 物化視圖 快速刷新 限制 說明

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

 

 

二．    物化視圖操作示例

 

1.     創(chuàng)建物化視圖需要的權(quán)限：

 

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

 

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

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

 

 

3.  授權(quán)給中間用戶  

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

 

4.  在目標(biāo)數(shù)據(jù)庫上創(chuàng)建MATERIALIZED VIEW  

 

CREATE MATERIALIZED VIEW AICS_DAVE
TABLESPACE&BISONCS_SPACE
REFRESH FAST
   ON DEMAND
   --第一次刷新時(shí)間
   --START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')
   START WITH SYSDATE
   --刷新時(shí)間間隔。每1天刷新一次，時(shí)間為凌晨2點(diǎn)
   --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 AICS_DAVE@LINK_DAVE;

 

5.  在目標(biāo)物化視圖上創(chuàng)建索引 

 

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 手工刷新

如：

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

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

            使用這種方式刷新之前需要先make refresh group，然后才可以刷新。

 

            Refreshmake 的語法可以參考：

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

 

示例：

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

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')

 

 

7. 刪除物化視圖及日志

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

 

 

8. 查看物化視圖刷新狀態(tài)信息

 

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

 

 

9. 查詢物化視圖日志：

 

SELECT * FROM MLOG$_DAVE;

 

 

posted on 2014-05-02 23:02 鴻雁 閱讀(166) 評(píng)論(0)  編輯  收藏 所屬分類: 數(shù)據(jù)庫