物化視圖詳解

相關內容參考：
Oracle 物化視圖 說明：
https://www.cnblogs.com/xiaohuilong/p/5995596.html

 
一、物化視圖概述
oracle的物化視圖是包括一個查詢結果的數據庫對像，它是遠程數據的的本地副本，或者用來生成基于數據表求和的匯總表。物化視圖存儲基于遠程表的數據，也可以稱為快照。

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

物化視圖可以查詢表，視圖和其它的物化視圖。通常情況下，物化視圖被稱為主表（在復制期間）或明細表（在數據倉庫中）。對于復制，物化視圖允許你在本地維護遠程數據的副本,這些副本是只讀的。如果你想修改本地副本，必須用高級復制的功能。當你想從一個表或視圖中抽取數據時，你可以用從物化視圖中抽取。對于數據倉庫，創建的物化視圖通常情況下是聚合視圖，單一表聚合視圖和連接視圖。在復制環境下，創建的物化視圖通常情況下主鍵，rowid,和子查詢視圖。

materialized view和普通VIEW有什么區別：

materialized view(MV)是自動刷新或者手動刷新的，View不用刷新 MV也可以直接update，但是不影響base table，對View的update反映到base table上 MV主要用于遠程數據訪問，mv中的數據需要占用磁盤空間，view中不保存數據

 

物化視圖由于是物理真實存在的，故可以創建索引。
 
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視圖對基表的影響可見一斑。

默認情況下，如果沒指定刷新方法和刷新模式，則oracle默認為force和demand。

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

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

默認情況下，如果沒指定刷新方法和刷新模式，則oracle默認為force和demand。

 
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上創建主鍵物化視圖：
create materialized view mv_emp_pk refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;
    
源庫：192.168.56.12 wang
目標庫：192.168.56.20 slient

源庫：
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

報錯，顯示要創建物化視圖日志；

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

顯示快速刷新無法創建目標庫基本的物化視圖日志，所以改一下刷新方式：由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.

方法二：顯示可以在目標庫創建基于基本的物化視圖日志，再在源庫創建物化視圖，如下：
源庫操作：
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

目標庫操作：
SQL> create materialized view log on emp;

Materialized view log created.

源庫再次創建：成功
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. --當用fast選項創建物化視圖，必須創建基于主表的視圖日志,如下:

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

報錯顯示創建本地物化視圖快速刷新機制要先建物化視圖日志，如下:

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.

成功創建。。。

--rowid物化視圖示例：

下面的語法在遠程數據庫表emp上創建rowid物化視圖

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

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

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

在遠程數據庫表emp上創建基于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 刷新時間

start with子句通知數據庫完成從主表到本地表第一次復制的時間,應該及時估計下一次運行的時間點, next 子句說明了刷新的間隔時間.

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.

在上面的例子中，物化視圖數據的第一個副本在創建時生成，以后每兩天刷新一次.

create materialized view mv_lvy_levydetaildata  
tablespace users --保存表空間  
build deferred--延遲刷新,不立即刷新  
refresh force--如果可以快速刷新則進行快速刷新，否則完全刷新  
on demand--按照指定方式刷新  
start with to_date('24-11-200518:00:10', 'dd-mm-yyyyhh34:mi:ss') --第一次刷新時間  
next trunc(sysdate+1)+18/24--刷新時間間隔  
as  
select * from emp@scottlink;   1.5 on prebuild table 說明

在創建物化視圖時指明on prebuild table語句，可以將物化視圖建立在一個已經存在的表上。這種情況下，物化視圖和表必須同名。當刪除物化視圖時，不會刪除同名的表。
這種物化視圖的查詢重寫要求參數query_rewrite_integerity必須設置為trusted或者stale_tolerated。

 
1.6 物化視圖分區

物化視圖可以進行分區。而且基于分區的物化視圖可以支持分區變化跟蹤（pct）。具有這種特性的物化視圖，當基表進行了分區維護操作后，仍然可以進行快速刷新操作。對于聚集物化視圖，可以在group by列表中使用cube或rollup，來建立不同等級的聚集物化視圖。
二、物化視圖操作示例

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

grant create materialized view to user_name;  

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

    create materialized view log on dave  
    tablespace&bisoncu_space           -- 日志空間  
    with primary key;                   -- 指定為主鍵類型  

3.  授權給中間用戶  

    grant select on dave to anqing;  
    grant select on mlog$_dave to anqing;  

4.  在目標數據庫上創建materialized view  

    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 hh34: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 aics_dave@link_dave;  

 
5.  在目標物化視圖上創建索引

    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

如果我們在創建物化視圖的過程指定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> 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. 刪除物化視圖及日志

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

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

物化視圖刪除的順序：
http://blog.itpub.net/4227/viewspace-242683/   8. 查看物化視圖刷新狀態信息
--查詢物化視圖信息：
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物化視圖日志結構:
http://blog.itpub.net/31397003/viewspace-2146803/