Oracle 物化檢視案例分享

一、簡介
物化檢視顧名思義就是物理化了檢視，使它實際儲存到了硬碟上，查詢效能能比普通的檢視好
二、物化檢視原理：
1、生成資料
兩大項：build immediate   build deferred
Build immediate:在建立物化檢視的同時根據主表生成資料
Bulid deferred:在建立物化檢視的同時，在物化檢視內不生成資料，如果此時沒有生成資料，以後可以採取：EXEC DBMS_MVIEW.Refresh(‘MV_name’,’C’)，注意必須使用全量重新整理，預設是增量重新整理，所以這裡引數必須是C，因為之前都沒有生成資料，所以必須全量。
2、關於重新整理
種重新整理方式：complete fast force
Complete :完全重新整理整個物化檢視，相當於重新生成物化檢視，此時即時增量重新整理可用也全量重新整理
Fast:當有資料更新時依照相應的規則對物化檢視進行更新（此時必須建立物化檢視日誌（物化檢視日誌記錄了資料更新的日誌）
Force:當增量重新整理可用則增量重新整理，當增量重新整理不可用，則全量重新整理（此項為預設選項）
不過從實際情況出發，應該儘量不使用預設項，可以考慮使用增量重新整理，對大表特別效，大表全量更新速度是非常慢的，特別是在存在索引的情況下（在建立物化檢視語句中，可能某些限制查詢的條件，導致了增量重新整理無法使用，這個是需要注意的，具體是哪類語句導致fast重新整理不可用，有待總結…..）


種重新整理時間：on demand on commit start with/ next
On demand:在需要重新整理時進行重新整理（人工判斷）
On commit:在基表上有提交操作時，進行更新
Start with:指定首次重新整理的時間（一般指定的是當前時間，不過也可以在建立物化檢視時不生成資料，則可以考慮在指定的時間重新整理，從而生成資料）
Next:重新整理的週期時間


實驗：
例項演示(分兩臺主機操作，主表主機和物化檢視主機)
1、建立表空間 (主表機器操作)
create tablespace tbs_mview datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\mview_tbs.dbf' size 100m autoextend on next 50m;
2、建立使用者(主表機器操作）
create user hurp_mv identified by "hurp_mv" default tablespace tbs_mview;
3、給使用者賦權（主表機器操作）
grant CREATE MATERIALIZED VIEW  to hurp_mv;
grant connect,resource to hurp_mv;
grant create table to hurp_mv;
4、使用者建立測試表(主表機器操作)
create table test(id int, name char(10));
5、插入資料(主表機器操作)
insert into test(id,name) values(1,'a');
insert into test(id,name) values(2,'a');
insert into test(id,name) values(3,'a');
insert into test(id,name) values(4,'a');
insert into test(id,name) values(5,'a');
insert into test(id,name) values(6,'a');
insert into test(id,name) values(7,'a');


6、備機上建使用者和表空間
CREATE TABLESPACE tbs_mview DATAFILE '/oradata/shrnc/mview_tbs.dbf'  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
create user hurp_mv identified by "hurp_mv" default tablespace tbs_mview;


grant connect,resource to hurp_mv;
grant create table to hurp_mv;
grant CREATE MATERIALIZED VIEW to hurp_mv;


7、建立DBLINK (物化檢視機器操作)
create database link  ora10g connect to hurp_mv identified by "hurp_mv" using 'master';
CREATE DATABASE LINK  to_test
CONNECT TO "sc" identified by "test"
using 'Sc_test';
8、建立檢視日誌(主表機器操作)
CREATE MATERIALIZED VIEW LOG ON test WITH rowid;
9、建立物化檢視 (物化檢視機器操作)
create MATERIALIZED VIEW test_view
REFRESH fast
ON demand  with primary key 
start with sysdate next sysdate+1/1440 as select id,name from hurp_mv.test@ora10g;


10、手動刷檢視
BEGIN
   DBMS_MVIEW.refresh (
      LIST=> 'test_view',
      METHOD        => 'F',
      PARALLELISM   => 1);
END;
/


注：這裡採用的是定時同步，需求可以實時同步


診斷：
begin
dbms_mview.explain_mview('select * from hurp_mv.test@ora10g');
end;
/
select capability_name, possible, msgtxt from mv_capabilities_table where capability_name like 'REFRESH%';




@?/rdbms/admin/utlxmv.sql






批次插入指令碼：
create or replace procedure batch_insert is 
v_sql02 varchar2(3999);
v_value number;
begin
for i in 1..10000 loop 
v_value:=i;
v_sql02:='insert into hurp_mv.test values(:i,''a''||:i)';
execute immediate v_sql02 using v_value,v_value;   
commit;                                            
end loop;
end;
/