物化视图快速刷新的一个问题

在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_commit
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.

查看目前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

往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(*)
----------
       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.

查看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

可以看到，随着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

查看一个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

--EOF--