在OraFAQ上看到一个关于物化视图快速刷新的问题，由于同一个mv log被多个物化视图使用，不同的物化视图使用不同的刷新间隔，导致mv log中记录过多而使得快速刷新变得缓慢。
下面通过一个实际的例子来演示这种情况：
创建测试表
NING@ning>create table master as select * from all_objects where object_id<100;
Table created.
NING@ning>alter table master add primary key(object_id);
Table altered.
Table created.
NING@ning>alter table master add primary key(object_id);
Table altered.
创建mv log
NING@ning>create materialized view log on master with primary key;
Materialized view log created.
Materialized view log created.
创建第一个mv，on commit刷新
NING@ning>create materialized view mv_commit
2 refresh fast
3 on commit
4 as
5 select object_id,object_type,object_name
6 from master;
Materialized view created.
2 refresh fast
3 on commit
4 as
5 select object_id,object_type,object_name
6 from master;
Materialized view created.
创建第二个mv，on demand刷新
NING@ning>create materialized view mv_demand
2 refresh fast
3 on demand
4 as
5 select object_id,LAST_DDL_TIME,STATUS
6 from master;
Materialized view created.
2 refresh fast
3 on demand
4 as
5 select object_id,LAST_DDL_TIME,STATUS
6 from master;
Materialized view created.
查看目前mv和mv log中的数据量
NING@ning>select count(*) from mv_commit;
COUNT(*)
----------
98
NING@ning>select count(*) from mv_demand;
COUNT(*)
----------
98
NING@ning>select count(*) from MLOG$_MASTER;
COUNT(*)
----------
0
COUNT(*)
----------
98
NING@ning>select count(*) from mv_demand;
COUNT(*)
----------
98
NING@ning>select count(*) from MLOG$_MASTER;
COUNT(*)
----------
0
往master表插入新的记录并commit
NING@ning>insert into master select * from all_objects where object_id>=100 and object_id<200;
99 rows created.
NING@ning>commit;
Commit complete.
99 rows created.
NING@ning>commit;
Commit complete.
查看mv和mv log中的数据量
NING@ning>select count(*) from mv_commit;
COUNT(*)
----------
197
NING@ning>select count(*) from mv_demand;
COUNT(*)
----------
98
NING@ning>select count(*) from mlog$_master;
COUNT(*)
----------
99
COUNT(*)
----------
197
NING@ning>select count(*) from mv_demand;
COUNT(*)
----------
98
NING@ning>select count(*) from mlog$_master;
COUNT(*)
----------
99
往master表插入新的记录并commit
NING@ning>insert into master select * from all_objects where object_id>=200 and object_id<300;
82 rows created.
NING@ning>commit;
Commit complete.
82 rows created.
NING@ning>commit;
Commit complete.
查看mv和mv log中的数据量
NING@ning>select count(*) from mv_commit;
COUNT(*)
----------
279
NING@ning>select count(*) from mv_demand;
COUNT(*)
----------
98
NING@ning>select count(*) from mlog$_master;
COUNT(*)
----------
181
COUNT(*)
----------
279
NING@ning>select count(*) from mv_demand;
COUNT(*)
----------
98
NING@ning>select count(*) from mlog$_master;
COUNT(*)
----------
181
可以看到，随着master不停的插入新的数据，mv_commit不停的被刷新，但是mv log中的记录了却没有在mv_commit刷新后清空，而是不停的增长。这是由于mv log中的数据对于mv_demand来说还是必须的，这样就导致mv_commit的刷新会越来越慢，因为快速刷新每次都是根据mv log中的记录来执行刷新的。
手动刷新一次mv_demand，可以发现mv log中的记录被清空了
NING@ning>exec dbms_mview.refresh('MV_DEMAND','F');
PL/SQL procedure successfully completed.
NING@ning>select count(*) from mv_demand;
COUNT(*)
----------
279
NING@ning>select count(*) from mlog$_master;
COUNT(*)
----------
0
PL/SQL procedure successfully completed.
NING@ning>select count(*) from mv_demand;
COUNT(*)
----------
279
NING@ning>select count(*) from mlog$_master;
COUNT(*)
----------
0
查看一个master表上有多少个mv及其最新刷新时间
NING@ning>SELECT owner, NAME, snapshot_site, TO_CHAR(current_snapshots,'mm/dd/yyyy hh24:mi') current_snapshots
2 FROM dba_registered_snapshots, dba_snapshot_logs
3 WHERE dba_registered_snapshots.snapshot_id = dba_snapshot_logs.snapshot_id (+)
4 AND dba_snapshot_logs.MASTER=upper('&table_name');
Enter value for table_name: master
old 4: AND dba_snapshot_logs.MASTER=upper('&table_name')
new 4: AND dba_snapshot_logs.MASTER=upper('master')
OWNER NAME SNAPSHOT_S CURRENT_SNAPSHOTS
---------- -------------------- ---------- --------------------
NING MV_COMMIT NING 07/16/2007 10:24
NING MV_DEMAND NING 07/16/2007 10:28
2 FROM dba_registered_snapshots, dba_snapshot_logs
3 WHERE dba_registered_snapshots.snapshot_id = dba_snapshot_logs.snapshot_id (+)
4 AND dba_snapshot_logs.MASTER=upper('&table_name');
Enter value for table_name: master
old 4: AND dba_snapshot_logs.MASTER=upper('&table_name')
new 4: AND dba_snapshot_logs.MASTER=upper('master')
OWNER NAME SNAPSHOT_S CURRENT_SNAPSHOTS
---------- -------------------- ---------- --------------------
NING MV_COMMIT NING 07/16/2007 10:24
NING MV_DEMAND NING 07/16/2007 10:28
不然你怎么判断上次刷到哪里了？
所有的记录! 笨.
index没有用的。mv刷新是根据mv log中所有的记录来执行fast刷新的
找找MV refresh 后台使用的 SQL UPDATE,
然后在 mv log 的 时间戳 或者 change sequence 列上面建立一个索引,
会不会加快 mv_commit的刷新.