Oracle物化視圖詳解

Oracle物化視圖詳解

現實工作中會有多個資料來源同步到一個資料庫完成資料分析的情境，這些資料可以不是即時同步的，我們一般通過定時任務抽取資料到統計分析庫給應用使用。

一般的同步方式可以通過時間戳記做全量和增量資料同步（存在原資料變化可能，資料不一致的情況），也可以通過dblink做資料即時查詢（較損耗線上資料庫效能），一般最好的方式是通過建立物化視圖，然後通過schedual job完成定時資料同步，這裡就記錄下物化視圖的使用。

一、物化視圖簡介

物化視圖是一種特殊的物理表，“物化”(Materialized)視圖是相對普通視圖而言的。普通視圖是虛擬表，應用的局限性大，任何對視圖的查詢，Oracle都實際上轉換為視圖SQL語句的查詢。這樣對整體查詢效能的提高，並沒有實質上的好處。

1、物化視圖分類

ON DEMAND：該物化視圖“需要”被重新整理了，才進行重新整理(REFRESH)，即更新物化視圖，以保證和基表資料的一致性；

ON COMMIT：一旦基表有了COMMIT，即事務提交，則立刻重新整理，立刻更新物化視圖，使得資料和基表一致；

預設情況建立物化視圖不指定類型，則是按需重新整理（on demand）

2、物化視圖

二、物化視圖使用

1、物化視圖建立

物化視圖的資料來源於基表，而重新整理的起始點記錄於物化視圖日誌，所以建立物化視圖授權必須有基表——>物化視圖日誌（基於基表）——>物化視圖

物化視圖建立樣本：

在dbtest下建立物化視圖T，其中基表是scott使用者下的dept表
（1）授權dbtest使用者可以查詢scott.dept
grant select on scott.dept to dbtest;
（2）在dbtest使用者下建立表T （若建立物化視圖加on prebuilt table）
create table t as select * from scott.dept where 1=2;
（3）在scott使用者下建立物化視圖日誌，在dbtest下建立物化視圖T
建立物化視圖日誌：
conn scott/tiger;
create materialized view log on dept;
grant select on MLOG$_DEPT to dbtest;
建立物化視圖：
conn dbtest/dbtest;
create materialized view T
on prebuilt table
refresh fast on demand
as
select deptno,dname,loc,ACOLUMN from scott.dept;

##可以通過在 view T後加上BUILD IMMEDIATE參數立刻重新整理物化視圖，得到資料

REFRESH 子句可以包含如下部分：
  [refresh [fast|complete|force]
  [on demand | commit]
  [start with date] [next date]
  [with {primary key|rowid}]]

2、物化視圖重新整理

當基表有更新後（DML），如果不是on commit類型，物化視圖需要重新整理後資料才能保持和基表一致，重新整理方式有全量重新整理（COMPLETE）、快速重新整理（增量FAST）、強制重新整理（FORCE）、不重新整理（NEVER）

FAST：增量快速重新整理

exec dbms_mview.refresh('表名', 'F') 

exec dbms_mview.refresh('dbtest.t','F');

COMPLETE：全量重新整理

exec dbms_mview.refresh('表名', 'C')  ;

exec dbms_mview.refresh('dbtest.t','C');

FORCE：重新整理時判斷否可以快速重新整理，如果能快速重新整理則執行fast重新整理，如果不能則執行complete重新整理

NEVER：不重新整理

3、物化視圖刪除

drop MATERIALIZED VIEW  mview_name;

4、物化視圖日誌刪除

物化視圖日誌是mlog$_basetablename命名格式

DROP MATERIALIZED VIEW LOG  on base_table_name;

MLOG$_DEPT

 DEPTNO          主鍵列
 SNAPTIME$$      用於表示重新整理時間
 DMLTYPE$$        用於表示dml操作類型，i表示insert，d表示delete，u表示update
 OLD_NEW$$        用於表示這個值是新值還是舊值。n（ew）表示新值（一般為delete操作），o（ld）表示舊值（一般為Insert操作），u表示update操作。
 CHANGE_VECTOR$$  表示修改向量，用來表示被修改的是哪個或哪幾個欄位
 XID$$             

如果with後面跟了primary key，則物化視圖日誌中會包含主鍵列。
如果with後面跟了rowid，則物化視圖日誌中會包含： m_row$$：用來儲存發生變化的記錄的rowid。
如果with後面跟了object id，則物化視圖日誌中會包含：sys_nc_oid$：用來記錄每個變化對象的對象id。
如果with後面跟了sequence，則物化視圖日子中會包含：sequence$$：給每個操作一個sequence號，從而保證重新整理時按照順序進行重新整理。
如果with後面跟了一個或多個column名稱，則物化視圖日誌中會包含這些列。 

當基本表發生dml操作時，會記錄到物化視圖日誌中，這時指定的時間4000年1月1日0時0分0秒（物化視圖未被重新整理）。
如果物化視圖日誌供多個物化視圖使用，則一個物化視圖重新整理後會將它重新整理的記錄的時間更新為它重新整理的時間。
只有建立快速重新整理的物化視圖才能使用物化視圖日誌，如果只建立一個物化視圖，則物化視圖重新整理完會將物化視圖日誌清除掉

--當建立物化視圖日誌使用primary key時，oracle建立暫存資料表 RUPD$_基礎資料表

5、查看物化視圖

複製代碼
set line 200;
set pagesize 20000;
col owner for a15;
col mview_name for a30;
col query for a60;

select owner,mview_name,refresh_method,last_refresh_date,compile_state from dba_mviews;

#如果要看具體語句，可以通過query欄位查看

三、附錄

物化視圖是一種特殊的物理表，“物化”(Materialized)視圖是相對普通視圖而言的。普通視圖是虛擬表，應用的局限性大，任何對視圖的查詢，Oracle都實際上轉換為視圖SQL語句的查詢。這樣對整體查詢效能的提高，並沒有實質上的好處。

https://www.bkjia.com/topicnews.aspx?tid=12

本文永久更新連結地址：https://www.bkjia.com/Linux/2018-02/151112.htm