物化視圖詳解

相關(guān)內(nèi)容參考：
Oracle 物化視圖 說(shuō)明：
https://www.cnblogs.com/xiaohuilong/p/5995596.html

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

物化視圖可以用于預(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)刷新。

物化視圖可以查詢表，視圖和其它的物化視圖。通常情況下，物化視圖被稱為主表（在復(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,和子查詢視圖。

materialized view和普通VIEW有什么區(qū)別：

materialized view(MV)是自動(dòng)刷新或者手動(dòng)刷新的，View不用刷新 MV也可以直接update，但是不影響base table，對(duì)View的update反映到base table上 MV主要用于遠(yuǎn)程數(shù)據(jù)訪問(wèn)，mv中的數(shù)據(jù)需要占用磁盤(pán)空間，view中不保存數(shù)據(jù)

 

物化視圖由于是物理真實(shí)存在的，故可以創(chuàng)建索引。
 
1.1 物化視圖可以分為以下三種類型

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

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

（3）嵌套物化視圖。

三種物化視圖的快速刷新的限制條件有很大區(qū)別，而對(duì)于其他方面則區(qū)別不大。創(chuàng)建物化視圖時(shí)可以指定多種選項(xiàng)，下面對(duì)幾種主要的選擇進(jìn)行簡(jiǎn)單說(shuō)明： （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）查詢重寫(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)一斑。

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

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

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。

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

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ī)制是通過(guò)物化視圖日志完成的。o racle通過(guò)一個(gè)物化視圖日志還可以支持多個(gè)物化視圖的快速刷新。

物化視圖日志根據(jù)不同物化視圖的快速刷新的需要，可以建立為rowid或primary key類型的。還可以選擇是否包括sequence、including new values以及指定列的列表。

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

 
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的物化視圖.

注意：創(chuàng)建物化視圖時(shí)默認(rèn)指定物化視圖中存在主鍵，如果不指定，那么創(chuàng)建的物化視圖日志文件的基表必須存在主鍵，否則會(huì)報(bào)錯(cuò)。

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

（1）distinct 或者聚合函數(shù).
（2）group by，子查詢，連接和set操作

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

在遠(yuǎn)程數(shù)據(jù)庫(kù)表emp上創(chuàng)建主鍵物化視圖：
create materialized view mv_emp_pk refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;
    
源庫(kù)：192.168.56.12 wang
目標(biāo)庫(kù)：192.168.56.20 slient

源庫(kù)：
sql> show user
user is "scott"
sql> create database link scottlink connect to scott identified by tiger using '  (description =
  2      (address = (protocol = tcp)(host = 192.168.56.20)(port = 1521))
  3      (connect_data =
  4        (server = dedicated)
  5        (service_name = service3 )
  6      )
  7    )';

database link created.

sql> select * from dba_db_links;

owner      db_link         username        host                                                                   created
---------- --------------- --------------- ---------------------------------------------------------------------- ------------
scott      scottlink       scott             (description =                                                       27-apr-18
                                               (address = (protocol = tcp)(host = 192.168.56.20)(port = 1521))
                                               (connect_data =
                                                 (server = dedicated)
                                                 (service_name = service3 )
                                               )
                                             )

sql> create materialized view MV_EMP_PK refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;
create materialized view mv_emp_pk refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink
*
error at line 1:
ora-23413: table "scott"."emp" does not have a materialized view log

報(bào)錯(cuò)，顯示要?jiǎng)?chuàng)建物化視圖日志；

SQL> create materialized view log on emp@SCOTTLINK;
create materialized view log on emp@SCOTTLINK
                                   *
ERROR at line 1:
ORA-00949: illegal reference to remote database

顯示快速刷新無(wú)法創(chuàng)建目標(biāo)庫(kù)基本的物化視圖日志，所以改一下刷新方式：由fast改為force,如下：

sql> create materialized view mv_emp_pk refresh force start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;

materialized view created.

方法二：顯示可以在目標(biāo)庫(kù)創(chuàng)建基于基本的物化視圖日志，再在源庫(kù)創(chuàng)建物化視圖，如下：
源庫(kù)操作：
SQL> create materialized view mv_emp_pk_2 refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;
create materialized view mv_emp_pk_2 refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink
                                                                                                                           *
ERROR at line 1:
ORA-23413: table "SCOTT"."EMP" does not have a materialized view log

目標(biāo)庫(kù)操作：
SQL> create materialized view log on emp;

Materialized view log created.

源庫(kù)再次創(chuàng)建：成功
SQL> create materialized view mv_emp_pk_2 refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;

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

SQL> create materialized view MV_EMP refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp;
create materialized view mv_emp refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp
                                                                                                                      *
ERROR at line 1:
ORA-23413: table "SCOTT"."EMP" does not have a materialized view log

報(bào)錯(cuò)顯示創(chuàng)建本地物化視圖快速刷新機(jī)制要先建物化視圖日志，如下:

SQL> create materialized view log on emp;

Materialized view log created.

SQL> create materialized view mv_emp refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp;

Materialized view created.

成功創(chuàng)建。。。

--rowid物化視圖示例：

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

SQL> create materialized view MV_EMP_ROWID  refresh with rowid  as select * from emp@scottlink;  

Materialized view created.
 
注意默認(rèn)的刷新條件是 force,默認(rèn)情況下，如果沒(méi)指定刷新方法和刷新模式，則oracle默認(rèn)為force和demand。
 

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

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

SQL> CREATE MATERIALIZED VIEW  MV_EMPDEPT AS SELECT * FROM emp@scottlink e WHERE EXISTS (SELECT * FROM dept@scottlink d WHERE e.DEPTNO = d.DEPTNO);

Materialized view created.

 
1.4.2 刷新時(shí)間

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

SQL> create materialized view MV_EMP_PK_1 refresh complete start with sysdate next sysdate + 2 with primary key as select * from emp@scottlink;  

Materialized view created.

在上面的例子中，物化視圖數(shù)據(jù)的第一個(gè)副本在創(chuàng)建時(shí)生成，以后每?jī)商焖⑿乱淮?

create materialized view mv_lvy_levydetaildata  
tablespace users --保存表空間  
build deferred--延遲刷新,不立即刷新  
refresh force--如果可以快速刷新則進(jìn)行快速刷新，否則完全刷新  
on demand--按照指定方式刷新  
start with to_date('24-11-200518:00:10', 'dd-mm-yyyyhh34:mi:ss') --第一次刷新時(shí)間  
next trunc(sysdate+1)+18/24--刷新時(shí)間間隔  
as  
select * from emp@scottlink;   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í)的聚集物化視圖。
二、物化視圖操作示例

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ù)庫(kù)上創(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 hh34: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. 物化視圖刷新說(shuō)明

（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 過(guò)程來(lái)批量刷新mv

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

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

refreshmake 的語(yǔ)法可以參考：
http://docs.oracle.com/cd/b19306_01/server.102/b14227/rarrefreshpac.htm#i94057

 
示例：
假設(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')   7. 刪除物化視圖及日志

--刪除物化視圖日志：  
drop materialized view log on dave;  

--刪除物化視圖  
drop materialized view mv_dave;  

物化視圖刪除的順序：
http://blog.itpub.net/4227/viewspace-242683/   8. 查看物化視圖刷新?tīng)顟B(tài)信息
--查詢物化視圖信息：
set lines 200
col OWNER for a10
col MVIEW_NAME a10
col QUERY for a30
col MASTER_LINK for a15   
col eligible_for_fast_refree for a25
col REWRITE_ENABLED for a15
col REFRESH_MODE for a15
col REFRESH_METHOD for a15
select a.OWNER,a.MVIEW_NAME,a.QUERY,a.MASTER_LINK,a.REWRITE_ENABLED,a.REFRESH_MODE,a.REFRESH_METHOD,a.BUILD_MODE,a.FAST_REFRESHABLE eligible_for_fast_refree,a.LAST_REFRESH_TYPE Method_for_recent_refresh,a.LAST_REFRESH_DATE,a.STALENESS Relationship,a.COMPILE_STATE Validity  from dba_mviews a where a.MVIEW_NAME in('MV_EMP_PK','MV_EMP','MV_EMP_ROWID','MV_EMPDEPT','MV_EMP_PK_1','MV_EMP_PK_2'); select * from dba_mview_refresh_times;  

--查詢物化視圖日志信息：
select * from dba_mview_logs
9. 查詢物化視圖日志：
select * from mlog$_dave;  

oracle物化視圖日志結(jié)構(gòu):
http://blog.itpub.net/31397003/viewspace-2146803/