在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.
创建mv log
NING@ning>create materialized view log on master with primary key;Materialized view log created.
创建第一个mv，on commit刷新
NING@ning>create materialized view mv_commit2 refresh fast3 on commit4 as5 select object_id,object_type,object_name6 from master;Materialized view created.
创建第二个mv，on demand刷新
NING@ning>create materialized view mv_demand2 refresh fast3 on demand4 as5 select object_id,LAST_DDL_TIME,STATUS6 from master;Materialized view created.
查看目前mv和mv log中的数据量
NING@ning>select count(*) from mv_commit;COUNT(*)---------- 98NING@ning>select count(*) from mv_demand;COUNT(*)---------- 98NING@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.
查看mv和mv log中的数据量
NING@ning>select count(*) from mv_commit;COUNT(*)---------- 197NING@ning>select count(*) from mv_demand;COUNT(*)---------- 98NING@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.
查看mv和mv log中的数据量
NING@ning>select count(*) from mv_commit;COUNT(*)---------- 279NING@ning>select count(*) from mv_demand;COUNT(*)---------- 98NING@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(*)---------- 279NING@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_snapshots2 FROM dba_registered_snapshots, dba_snapshot_logs3 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: masterold 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:24NING MV_DEMAND NING 07/16/2007 10:28
--EOF--
