利用PCT解决快速刷新性能问题

在数据仓库的一个应用中，有一个这样的案例：

1. 源表很大，且是根据天做的分区表
2. 正常情况下，每天凌晨往源表一次性导入上一天的数据
3. 正常情况下，数据导入后不会做任何的修改和删除操作
4. 有其他的应用需要对这个源表的数据做聚合查询(group by)，其他一个维度是分区键
[@more@] PCT快速刷新是Oracle的的一个新特性，可以让基于分区表的物化视图在不需要物化视图日志的情况下，实现快速刷新。当然这种刷新的粒度是分区级的，不能精确到行级。要启用PCT快速刷新，无需特别设置。
原来其他同事创建了物化视图日志来实现快速刷新，实际上，分析一下上面的例子，用物化视图日志来刷新非常不合理，因为每次刷新都需要刷新一整个分区的数据，刷新效率不高，且物化视图日志对数据导入的性能影响还是很大的。
其实上面的情形很适用于PCT刷新，因此可以用PCT快速刷新来解决以上问题，使得既可以做到快速刷新，又可以减少mlog带来的额外开销。

--首先创建一个分区表
SQL> create table test
2 (
3 a date,
4 b char(20),
5 c char(20),
6 d char(20)
7 )
8 partition by range(a)
9 (
10 partition partition_2009 values less than(to_date('2010-01-01','yyyy-mm-dd')),
11 partition partition_2010 values less than(to_date('2011-01-01','yyyy-mm-dd')),
12 partition partition_2011 values less than(to_date('2012-01-01','yyyy-mm-dd'))
13 );

Table created

--创建物化视图日志
SQL> create materialized view log on test with rowid;

Materialized view log created

SQL> create materialized view mv_test refresh fast on demand as
2 select a,count(1) cnt from
3 test
4 group by a;

Materialized view created

--写入大量数据
SQL> insert into test select to_date('2009-01-01','yyyy-mm-dd'),'b','c','d' from dual connect by rownum<=5000000;

已创建5000000行。

已用时间: 00: 04: 11.21

统计信息
----------------------------------------------------------
5017881 recursive calls
10877553 db block gets
124205 consistent gets
9 physical reads
2978381980 redo size
680 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
5000000 rows processed

SQL> commit;

用时很长，花了4分多钟，redo量接近3000M。

--快速刷新
SQL> exec dbms_mview.refresh('mv_test','f')

PL/SQL 过程已成功完成。

已用时间: 00: 08: 03.40

--花了8分钟
SQL> drop materialized view log on test;

实体化视图日志已删除。

--在没有物化视图日志的情况下写入大量数据
SQL> insert into test select to_date('2010-01-01','yyyy-mm-dd'),'b','c','d' from dual connect by rownum<=5000000;

已创建5000000行。

已用时间: 00: 00: 34.23

统计信息
----------------------------------------------------------
7800 recursive calls
457969 db block gets
103279 consistent gets
25 physical reads
434920672 redo size
682 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
35 sorts (memory)
0 sorts (disk)
5000000 rows processed

--只用了34秒钟就完成了，日志量也只产生了430M，和刚才比，效率提升七八倍。

--看看是否能快速刷新
SQL> exec dbms_mview.refresh('mv_test','f')

已用时间: 00: 00: 05.50

SQL> exec dbms_mview.refresh('mv_test','c')

已用时间: 00: 00: 11.75


对比一下快速刷新和全部刷新的时间，可以知道，快速刷新涉及到的数据量肯定比完全刷新少，间接证明了不用物化视图日志也可以实现分区级别的快速刷新。


SQL> update test set a= to_date('2010-01-01','yyyy-mm-dd') where a= to_date('2010-01-01','yyyy-mm-dd') and rownum=1;

SQL> commit;

SQL> exec dbms_mview.refresh('mv_test','f')

已用时间: 00: 00: 05.54

我们只更新了一条数据，但却需要近6秒完成快速刷新，说明整个增量刷新是基于分区级别的，而不是行级的增量。


当然，上面的证明方法比较简单，大家如有兴趣，可以用10046跟踪一下刷新过程，就可以知道PCT是怎么实现快速刷新的了。

最后要说明的是：如果物化视图包含group by语句，则要实现PCT增量刷新，一个必须条件是分区键包含在group by子句中。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-1044604/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-1044604/