Configure and manage distributed materialized views

一、物化视图的分类：
Materialized Views with Aggregates        --聚合计算
Materialized Views Containing Only Joins  --连接
Nested Materialized Views                 --嵌套物化视图可以嵌套基于其他物化视图，就是被嵌套的可以当表来使
 
二、刷新方式：
complete refresh --完全刷新
类似普通view，每次刷新是进行全量刷新，可以不需要物化视图日志。
fast refresh/incrementnal refresh  --快速刷新，又叫增量量刷新
需要使用物化视图日志（materialized view log）
该日志是建立在Master Table上的表的变化日志
Force refresh    --强制刷新
可以fast时候fast，不可以的时候complete，默认方式
 
三、刷新频率：
On demand
按需刷新，即手动(人为地在外部定制计划)
On Commit
master表在commit之后就进行刷新
物化视图与Master Table在同一数据库
Start with/next
指定刷新间隔时间，使用这种方式建立的物化视图，会自动创建一个job进行定时刷新。
 
四、创建一个简单的快速刷新物化视图
建立一个能快速刷新的物化视图，且能去除表中的重复行。
 
1.创建master表：
create table t1(userid,username) as select user_id,username from dba_users;
insert into t1 select * from t1;
 
2.创建物化视图日志：
create materialized view log on t1
with rowid,sequence(userid,username) including new values;
 
3.创建物化视图：
create materialized view mv_t1 refresh fast on commit
as
select userid,username,count(*) from t1
group by userid,username;
 
五、创建一个分发的物化视图，通过dblink
1.创建一个dblink
create public database link test_ora10g connect to test identified by test using 'ora10g';
 
2.创建通过dblink的物化视图
create materialized view mv_t1
refresh fast with rowid
start with sysdate
next sysdate + 1/(24*60)
as
select userid,username,count(*) from t1@test_ora10g
group by userid,username;
 
3.刷新物化视图
begin
dbms_mview.refresh('ldy.mv_t1', 'F' );
end;
/
 
4.创建物化视图刷新组
BEGIN
dbms_refresh.make(
name => 'mv1_refgroup',
list => 'mv_t1',
next_date => sysdate,
interval => 'sysdate + 1',
implicit_destroy => true,
lax => true);
END;
/
 
5.刷新一组物化视图
exec dbms_refresh.refresh(name => 'mv1_refgroup');
 
六、检查物化视图
用户需要具有dbms_mview的执行权限
grant execute on dbms_mview to ;
 
SQL> @ORACLE_HOME/rdbms/admin/utlxmv.sql
SQL> EXEC DBMS_MVIEW.EXPLAIN_MVIEW ('SELECT * FROM SCOTT.EMP');
SQL> set linesize 220
SQL> SELECT SUBSTR(capability_name,1,30)AS capability_name ,  possible, SUBSTR(related_text,1,10)AS related_text,
 SUBSTR(msgtxt,1,90)AS msgtxt FROM MV_CAPABILITIES_TABLE where capability_name like 'REFRESH%'ORDER BY seq;
 
 
CAPABILITY_NAME                P RELATED_TEXT     MSGTXT
------------------------------ - --------------- ------------------------------------------------------------------------------------------
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REFRESH_FAST_AFTER_INSERT      N SCOTT.EMP       the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML  N                 see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     N                 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N                 PCT is not possible on any of the detail tables in the materialized view
 
6 rows selected.