物化视图MVIEW的使用

一、物化视图原理

物化视图就是普通视图的实体化，普通视图在每次用的时候相当于重新查询，而物化视图相当于把普通视图的结果，放在物化视图表中，这个再给物化视图加上索引那么查询效率不是一般的块。注意：自动刷新需要首先在基表创建日志，删除物化视图时必须先删除日志。

二、物化视图使用

创建物化视图需要的权限：

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点刷新 

手动刷新物化视图：

dbms_mview.refresh('MV_AC01_CC03', 'C'); 

建立索引：

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; --删除物化视图 


例子：
===============创建日志=================
create materialized view log on AC01 tablespace FSHRDATA with primary key;
create materialized view log on Ab01 tablespace FSHRDATA with primary key;
create materialized view log on cc03 tablespace FSHRDATA with primary key;
create materialized view log on cc02 tablespace FSHRDATA with primary key;
===============MV_AC01_CC03=============
create materialized view MV_AC01_CC03 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') 
as
select t1.aae100,t1.aac001,t1.aab001,t1.aae022,t1.adc110,t1.acc036,t3.aac004,t3.aac005,t3.aac006,t3.aac011,t3.aac009,t3.aab299 from ac01 t3, cc03 t1 where t1.aae100 = '1' and t1.aac001 = t3.aac001;


create index IDX_MV_AC01_CC03 
on MV_AC01_CC03 (AAE022, ADC110, acc036,AAC009, AAB299, AAC004, AAC005, AAC006, AAC011)
tablespace FSHRDATA;
===============MV_AC01_CC02=============
create materialized view MV_AC01_CC02 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') 
as
select t1.aae100,t1.aac001,t1.aae022,t1.adc210,t1.acc036,t3.aac004,t3.aac005,t3.aac006,t3.aac011,t3.aac009,t3.aab299 from ac01 t3, cc02 t1 where t1.aae100 = '1' and t1.aac001 = t3.aac001;


create index IDX_MV_AC01_CC02
on MV_AC01_CC02(AAE022, ADC210,acc036,AAC009, AAB299, AAC004, AAC005, AAC006, AAC011) 
tablespace FSHRDATA;
===============MV_AB01_CC03=============
create materialized view MV_AB01_CC03 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') 
as
select t1.aae100,t1.aac001,t1.aab001,t1.aae022,t1.adc110,t2.aab022,t2.aab019,t2.aab020 from ab01 t2, cc03 t1 where t1.aae100 = '1' and t1.aab001 = t2.aab001;


create index IDX_MV_AB01_CC03 
on MV_AB01_CC03 (AAE022, ADC110, AAB022, AAB019, AAB020)
tablespace FSHRDATA;


===============MV_AC01_AB01_CC03=============
create materialized view MV_AC01_AB01_CC03 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') 
as
select t1.aae100,t1.aab001,t1.aac001,t1.aae022,t1.adc110,t2.aab022,t2.aab019,t2.aab020,t3.aac009,t3.aab299 from ac01 t3, ab01 t2, cc03 t1 where t1.aae100 = '1' and t1.aab001 = t2.aab001 and t1.aac001 = t3.aac001;


create index IDX_MV_AC01_AB01_CC03
on MV_AC01_AB01_CC03(aae022,adc110,aab022,aab019,aab020,aac009,Aab299)  
tablespace FSHRDATA;
=======================删除物化视图===============


drop materialized view log on ac01;
drop materialized view log on ab01;
drop materialized view log on cc03;
drop materialized view log on cc02;
drop materialized view MV_AC01_CC03;
drop materialized view MV_AC01_CC02;
drop materialized view MV_AB01_CC03;
drop materialized view MV_AC01_AB01_CC03;
=======================手动刷新物化视图============================
dbms_mview.refresh('MV_AC01_CC03', 'C'); 
dbms_mview.refresh('MV_AC01_CC02', 'C'); 
dbms_mview.refresh('MV_AB01_CC03', 'C');
dbms_mview.refresh('MV_AC01_AB01_CC03', 'C');