Oracle物化檢視的建立及使用（一）

Oracle物化檢視的建立及使用
http://blog.csdn.net/tegwy/article/details/8935058

先看簡單建立語句：

create   materialized   view  mv_materialized_test  refresh   force   on   demand   start   with   sysdate   next

to_date( concat (to_char(  sysdate + 1 , 'dd-mm-yyyy' ), '10:25:00' ), 'dd-mm-yyyy hh24:mi:ss' )  as

select  *  from  user_info;  --這個物化檢視在每天10：25進行重新整理

 

物化檢視也是種檢視。 Oracle的物化檢視是包括一個查詢結果的資料庫對像，它是遠端資料的的本地副本，或者用來生成基於資料表求和的彙總表。物化檢視儲存基於遠端表的資料，也可以稱為快照。

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

 

特點：

(1) 物化檢視在某種意義上說就是一個 物理表 (而且不僅僅是一個物理表)，這透過其可以被user_tables查詢出來，而得到確認；

(2) 物化檢視也是一種段(segment)，所以其有自己的物理儲存屬性；

(3) 物化檢視會佔用資料庫磁碟空間，這點從user_segment的查詢結果，可以得到佐證；

建立語句：create materialized view mv_name as select * from table_name

因為物化檢視由於是物理真實存在的，故可以建立索引。

 

建立時生成資料：

分為兩種： build immediate 和 build deferred ，

build immediate是在 建立物化檢視的時候就生成資料 。

build deferred 則在建立時不生成資料，以後根據需要在生成資料 。

如果不指定，則預設為 build immediate 。

 

重新整理模式：

物化檢視有二種重新整理模式：

在建立時refresh mode是 on demand 還是 on commit 。

on demand 顧名思義，僅在該物化檢視“需要”被重新整理了，才進行重新整理(REFRESH)，即更新物化檢視，以保證和基表資料的一致性;

on commit  提交觸發，一旦基表有了commit，即事務提交，則立刻重新整理，立刻更新物化檢視，使得資料和基表一致。一般用這種方法在操作基表時速度會比較慢。

建立物化檢視時未作指定，則Oracle按 on demand 模式來建立。

 

上面說的是 重新整理模式 ，針對於如何重新整理，則有如下三種 重新整理方法 ：

 

完全重新整理（COMPLETE）： 會刪除表中所有的記錄（如果是單表重新整理，可能會採用TRUNCATE的方式），然後根據物化檢視中查詢語句的定義重新生成物化檢視。

快速重新整理（FAST）： 採用增量重新整理的機制，只將自上次重新整理以後對基表進行的所有操作重新整理到物化檢視中去。FAST必須建立基於主表的檢視日誌。對於增量重新整理選項，如果在子查詢中存在分析函式，則物化檢視不起作用。

FORCE方式： 這是預設的資料重新整理方式。Oracle會自動判斷是否滿足快速重新整理的條件，如果滿足則進行快速重新整理，否則進行完全重新整理。

 

關於快速重新整理： Oracle物化檢視的 快速重新整理 機制是透過 物化檢視日誌 完成的。 Oracle透過一個物化檢視日誌還可以支援多個物化檢視的快速重新整理 。 物化檢視日誌根據不同物化檢視的快速重新整理的需要，可以建立為ROWID或PRIMARY KEY型別的 。還可以選擇是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

 

查詢重寫（QueryRewrite）：

包括 enable query rewrite 和 disable query rewrite 兩種。

分別指出建立的物化檢視是否支援查詢重寫。 查詢重寫是指當對物化檢視的基表進行查詢時 ， oracle會自動判斷能否透過查詢物化檢視來得到結果，如果可以，則避免了聚集或連線操作，而直接從已經計算好的物化檢視中讀取資料。

預設為 disable query rewrite 。

 

語法：

create  materialized  view   view_name
refresh  [ fast|complete|force ]
[
on [commit|demand ]   |
start  with  (start_time)  next  (next_time)

]

AS subquery;

 

具體操作

 建立物化檢視需要的許可權：

grant   create   materialized   view   to  user_name; 

 

在源表建立 物化檢視日誌 ：

create   materialized   view   log   on  test_table  

tablespace  test_space  -- 日誌空間  

with   primary   key ;      -- 指定為主鍵型別

 

在目標資料庫上建立MATERIALIZED VIEW：

create   materialized   view  mv_materialized_test  refresh   force   on   demand   start   with   sysdate   next

to_date( concat (to_char( sysdate + 1 , 'dd-mm-yyyy' ), '10:25:00' ), 'dd-mm-yyyy hh24:mi:ss' )  as

select  *  from  user_info;  --這個物化檢視在每天10：25進行重新整理 

 

修改重新整理時間：

alter   materialized   view  mv_materialized_test  refresh   force   on   demand   start   with   sysdate  

next  to_date( concat (to_char( sysdate + 1 , 'dd-mm-yyyy' ), ' 23:00:00' ), 'dd-mm-yyyy hh24:mi:ss' );

或

alter   materialized   view  mv_materialized_test  refresh   force   on   demand   start   with   sysdate  

next   trunc ( sysdate , 'dd' )+ 1 + 1 / 24 ;  -- 每天1點重新整理 

建立索引：

create   index  IDX_MMT_IU_TEST

on  mv_materialized_test( ID ,UNAME)  

tablespace  test_space; 

刪除物化檢視及日誌：

drop   materialized   view   log   on  test_table;     --刪除物化檢視日誌： 

drop   materialized   view  mv_materialized_test;  --刪除物化檢視