一、物化视图的分类:
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.
阅读(1172) | 评论(0) | 转发(0) |