oracle 10g_物化视图及物化视图日志刷新解惑小记_materialized view
个人简介：
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院
河北廊坊新奥集团公司
项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
联系方式：
手机：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/
今天同事问及关于物化视图及物化视图日志刷新的相关问题，聊天内容如下：
|
hi，在？
wisdomone1 14:47:28
在？
同事 14:47:45
问你个关于物化视图的
同事 14:47:49
有点疑惑
wisdomone1 14:47:55
好的
wisdomone1 14:47:57
相互交流
同事 14:48:17
咱物化视图日志 是时时更新的吗
wisdomone1 14:48:41
应该不是吧，只要基本有变化，才会更新吧
同事 14:49:02
物化视图 是fast on demand
同事 14:49:41
基表是时时在变化 做插入数据操作
同事 14:49:59
但是物化视图是每4小时刷新一次
wisdomone1 14:50:22
这样就有数据延迟了哟
wisdomone1 14:50:37
只有这个物化视图日志是这种情况吗
wisdomone1 14:50:40
其它的呢
wisdomone1 14:50:42
也是一样
同事 14:50:52
然后我的疑惑是：物化视图日志是等物化视图刷新时间到了，才会刷新，还是平常，只有基表有数据更新，都会刷新
 
恩，如果是这样的话，就不知道这和on fast有啥区别了
同事 15:07:59
on commit
|
2，测试步骤
- 创建物化视图语法
@refresh with primary key，它是物化视图默认的创建方式，它允许其源表重组，而不会影响物化
视图的增量刷新；
如采用此种方式创建物化视图，源表必须包括主键列，并且物化视图的查询列必须包括源表的所有主键列；
即创建物化视图不能对主键列进行函数转换，如：upper(主键列）
@refresh with primary key的限制条件，不能适用于对象物化视图，对象物化视图默认是基于refresh with object id
@refresh with rowed,它适用于物化视图定义不包括源表所有的主键列
@refresh with rowid的限制条件:
@基于单表
@不能包括如下：
@ 聚集函数，如distinct,sum
@group by ,connect by
@subquery
@join
@set operators
@如果源表重组，则基于refresh with rowed的物化视图不能继续增量刷新，必须进行一次全量刷新，
才能继续进行增量刷新
@对象物化视图不适用于refresh with rowid,对象物化视图默认采用with object id
@never refresh表明暂时禁用对于化视图的刷新，即忽略dbms_mview.refresh，如果想解禁对于物化视图的
刷新，运行alter materialized view refresh
-
如下是我之前关于物化视图日志的测试文章：
- 数据库版本
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
-
创建测试表并插入数据
Table created.
SQL> insert into t_mv select level,level from dual connect by level<=1;
1 row created.
SQL> commit;
Commit complete.
-
创建物化视图日志
SQL> create materialized view log on t_mv with rowid;
Materialized view log created.
-
创建物化视图
SQL> create materialized view mv_t_mv refresh fast on demand start with sysdate next sysdate+1/24*60 with rowid as select a,b from t_mv;
Materialized view created.
-
查看物化视图信息
user_mviews官方说明
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> col mview_name for a15
SQL> col owner for a20
SQL> set linesize 300
SQL> select owner,mview_name,updatable,update_log,build_mode,fast_refreshable,last_refresh_type,last_refresh_date,staleness,stale_since from user_mviews where lower(mview_name)='mv_t_mv';
OWNER MVIEW_NAME U UPDATE_LOG BUILD_MOD FAST_REFRESHABLE LAST_REF LAST_REFRESH_DATE STALENESS STALE_SINCE
-------------------- --------------- - ------------------------------ --------- ------------------ -------- ------------------- ------------------- -------------------
TBS_11204 MV_T_MV N IMMEDIATE DML FAST 2014-08-12 10:58:45 FRESH
SQL>
-
查看物化视图中的数据
SQL> select a,b from mv_t_mv;
A B
---------- ----------
1 1
-
查看物化视图的信息
物化视图日志的官方说明
ALL_MVIEW_LOGSALL_MVIEW_LOGS describes all materialized view logs accessible to the current user. Related Views
|
-
查看物化视图日志信息
SQL> col log_table for a20
SQL> col master for a10
SQL> select master,log_table,rowids,primary_key,object_id,sequence from user_mview_logs where lower(master)='t_mv';
MASTER LOG_TABLE ROW PRI OBJ SEQ
---------- -------------------- --- --- --- ---
T_MV MLOG$_T_MV YES NO NO NO
-
查看测试表的物化视图日志的表结构
SQL> set linesize 50
SQL> desc mlog$_t_mv;
Name Null? Type
----------------------- -------- ----------------
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
SQL> select count(*) from t_mv;
COUNT(*)
----------
1
-
向源表插入数据
SQL> insert into t_mv select level,level from dual connect by level<=200000;
SQL> select count(*) from mlog$_t_mv;
200000 rows created.
SQL> commit;
-
查看物化视图日志的记录
SQL> select count(*) from mlog$_t_mv;
COUNT(*)
----------
200000
Commit complete.
-
物化视图日志图的结构
SQL> desc mlog$_t_mv;
Name Null? Type
----------------------------------------- -------- ----------------------------
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
-
查看物化视图日志表的内容
Session altered.
SQL> col change_vector$$ for a10
SQL> col m_row$$ for a20
SQL> select m_row$$,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_mv where rownum<=20;
M_ROW$$ SNAPTIME$$ D O CHANGE_VEC
-------------------- ------------------- - - ----------
AAALreAAGAAAJfcAAB 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAC 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAD 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAE 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAF 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAG 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAH 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAI 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAJ 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAK 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAL 4000-01-01 00:00:00 I N FE
M_ROW$$ SNAPTIME$$ D O CHANGE_VEC
-------------------- ------------------- - - ----------
AAALreAAGAAAJfcAAM 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAN 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAO 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAP 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAQ 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAR 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAS 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAT 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAU 4000-01-01 00:00:00 I N FE
20 rows selected.
SQL> select m_row$$,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_mv where m_row$$='AAALreAAGAAAJfcAAQ';
M_ROW$$ SNAPTIME$$ D O CHANGE_VEC
-------------------- ------------------- - - ----------
AAALreAAGAAAJfcAAQ 4000-01-01 00:00:00 I N FE
SQL> select a,b,rowid from t_mv where rowid='AAALreAAGAAAJfcAAQ';
A B ROWID
---------- ---------- ------------------
16 16 AAALreAAGAAAJfcAAQ
-
更新上述rowid对应的源表记录
SQL> update t_mv set a=116 where rowid='AAALreAAGAAAJfcAAQ';
1 row updated.
SQL> commit;
Commit complete.
-
查看物化视图日志表内容
A B
---------- ----------
116 16
SQL> select m_row$$,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_mv where m_row$$='AAALreAAGAAAJfcAAQ';
M_ROW$$ SNAPTIME$$ D O CHANGE_VEC
-------------------- ------------------- - - ----------
AAALreAAGAAAJfcAAQ 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAQ 4000-01-01 00:00:00 U U 02
-
删除上述源表记录
SQL> delete from t_mv where rowid='AAALreAAGAAAJfcAAQ';
1 row deleted.
SQL> commit;
Commit complete.
-
查看物化视图日志内容
SQL> delete from t_mv where rowid='AAALreAAGAAAJfcAAQ';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select a,b from t_mv where rowid='AAALreAAGAAAJfcAAQ';
no rows selected
SQL> select m_row$$,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_mv where m_row$$='AAALreAAGAAAJfcAAQ';
M_ROW$$ SNAPTIME$$ D O CHANGE_VEC
-------------------- ------------------- - - ----------
AAALreAAGAAAJfcAAQ 4000-01-01 00:00:00 I N FE
AAALreAAGAAAJfcAAQ 4000-01-01 00:00:00 U U 02
AAALreAAGAAAJfcAAQ 4000-01-01 00:00:00 D O 00
-
截断源表
SQL> truncate table t_mv;
Table truncated.
-
查看物化视图日志
SQL> select m_row$$,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_mv where m_row$$='AAALreAAGAAAJfcAAQ';
no rows selected
SQL> select m_row$$,snaptime$$,dmltype$$,old_new$$,change_vector$$ from mlog$_t_mv;
no rows selected
小结
- snaptime$$表明一个时间戳,且是远大于当前日期的一个日期 4000-01-01 00:00:00
- dmltype$$表操作的类型，比如：insert为I,猜测delete为D，update为U,truncate为T
- old_new$$表明当前的值是旧值或是新值
- m_row$$表明源表记录的rowid
- 如果源表先是插入，后是更新，会在物化视图日志产生2条记录；
- 如果源表先是持入，次是更新，最后删除，会在物化视图日志产生3条记录
- 物化视图日志其实就是源表变化的历史表
继续测试，插入源表记录
SQL> insert into t_mv select level,level from dual connect by level<=10000;
10000 rows created.
SQL> select count(*) from t_mv;
COUNT(*)
----------
10000
SQL> select count(*) from mlog$_t_mv;
COUNT(*)
----------
10002
SQL> select count(*) from mv_t_mv;
COUNT(*)
----------
1
all_mview_refresh_times官方说明
ALL_MVIEW_REFRESH_TIMESALL_MVIEW_REFRESH_TIMES describes refresh times of the materialized views accessible to the current user. Related Views
|
SQL> select owner,name,master_owner,master,last_refresh from dba_mview_refresh_times;
OWNER NAME MASTER_OWNER MASTER LAST_REFRESH
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------------
TBS_11204 MV_T_MV TBS_11204 T_MV 2014-08-12 10:58:45
SQL> host date
Wed Aug 13 10:07:43 EDT 2014
小结：
如果源表数据变更，马上会时时同步到物化视图日志中
如果源表数据变更，马上会时时同步到物化视图日志中
物化视图不会时时刷新，和源表的数据保持同步
on demand及start with只会定时刷新物化视图
-
继续测试，创建on commit方式的物化视图
SQL> create materialized view mv_t_mv_second refresh fast on commit with rowid as select a,b from t_mv;
Materialized view created.
源表插入1条记录
SQL> insert into t_mv values(1,2);
1 row created.
SQL> commit;
Commit complete.
查看源表
SQL> select count(*) from mv_t_mv_second;
SQL> select count(*) from mv_t_mv_second;
COUNT(*)
----------
10001
查看物化视图
SQL> select count(*) from mv_t_mv_second;
COUNT(*)
----------
10001
删除源表
SQL> delete from t_mv where rownum<=2000;
2000 rows deleted.
SQL> commit;
Commit complete.
查看源表
SQL> select count(*) from t_mv;
COUNT(*)
----------
8001
查看物化视图
SQL> select count(*) from mv_t_mv_second;
COUNT(*)
----------
8001
SQL>
更新源表
SQL> update t_mv set a=99;
1 row updated.
SQL> commit;
Commit complete.
查看物化视图
SQL> select * from mv_t_mv_second;
A B
---------- ----------
99 1
截断源表
SQL> truncate table t_mv;
Table truncated.
SQL> select * from t_mv;
no rows selected
查看物化视图
SQL> select * from mv_t_mv_second;
A B
---------- ----------
99 1
SQL> select * from mlog$_t_mv;
no rows selected
截断源表插入源表数据，提交时报错
SQL> insert into t_mv values(2,2);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-12034: materialized view log on "TBS_11204"."T_MV" younger than last refresh
对物化视图进行全量刷新
SQL> exec dbms_mview.refresh('mv_t_mv_second','c');
PL/SQL procedure successfully completed.
提交，之前失败的事务会丢失
SQL> commit;
Commit complete.
SQL> select * from t_mv;
no rows selected
重新运行之前的插入操作即可
SQL> insert into t_mv values(2,2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_mv;
A B
---------- ----------
2 2
同步到物化视图
SQL> select * from mv_t_mv_second;
A B
---------- ----------
2 2
物化视图回复到之前，可以实时与源表保持同步
SQL> delete from t_mv;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from mv_t_mv_second;
no rows selected
小结：
on commit方式创建的物化视图，会马上把源表insert,delete,update的数据变更同步到物化视图
truncate源表，不支持马上把源表数据同步到物化视图，需要对物化视图进行全量刷新，尔后会恢复到实时刷新状态
参考资料：
oracle10g 官方手册： http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#CIHBIEII
之前关于物化视图相关的一些测试文章：http://blog.itpub.net/9240380/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30373263/viewspace-2125323/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Linux系统CPU信息
下一篇：
oracle限制某个用户最大连接数
请登录后发表评论
登录
全部评论
