对物化视图的利用

本主题被作者加入到个人文集中

spring1234

临时用户组

社区积分: 0
技术积分: 5

大中小 1楼

2008-3-21 20:33 只看该作者

对物化视图的利用

2008年3月20日20:07:45

一般形式：

create materilized view view_name
bulid immediate
refresh on commit|complete start with sysdata next trunc(sysdate+1) +4/24  --refresh 的频率
enable query rewrite
as
body  --the content of MV
;

作用：
通过预计算或汇总构建自己的独立存储，从而提高查询的性能。通常可以减少cpu、内存空间、temp空间的使用。这是典型的以空间换效率的手段。

示例：(摘自《expert one-by-one oracle》一书
SQL> create table tt as select * from dba_objects;

表已创建。

SQL> insert /*+append*/ into tt select * from tt;

已创建48149行。

SQL> commit;

提交完成。

SQL> insert /*+append*/ into tt select * from tt;

已创建96298行。

SQL> commit
  2  /

提交完成。

SQL> insert /*+append*/ into tt select * from tt;

已创建192596行。

SQL> commit;

提交完成。

SQL> insert /*+append*/ into tt select * from tt;

已创建385192行。

SQL> commit;

提交完成。

SQL> set timing on
SQL> set autotrace traceonly exp statistics
SQL> select  owner, count(*) from tt
  2  group by owner;

已选择25行。

已用时间:  00: 00: 01.93

执行计划
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2439 Card=867230 B
      ytes=14742910)

1 0 SORT (GROUP BY) (Cost=2439 Card=867230 Bytes=14742910)
2 1    TABLE ACCESS (FULL) OF 'TT' (TABLE) (Cost=2367 Card=8672       ------  进行了全表扫描
      30 Bytes=14742910)

统计信息
----------------------------------------------------------
      6  recursive calls
      1  db block gets
   10684  consistent gets
   10647  physical reads
      168  redo size
      970  bytes sent via SQL*Net to client
      523  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      25  rows processed
SQL> create table tt2 as  select * from sys.tt nologging;

表已创建

SQL>  create materialized view tt_arg
2  build immediate
3  refresh on commit
4  enable query rewrite
5  as
6  select owner, count(*) from tt2
7 group by owner
8 /

实体化视图已创建。

SQL> analyze table tt compute statistics;

表已分析。

已用时间:  00: 00: 19.17

SQL> select owner, count(*) from tt
  2  group by owner;

已选择25行。

已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=25 Bytes=75
      0)

1 0 MAT_VIEW REWRITE ACCESS (FULL) OF 'TT_ARG' (MAT_VIEW REWRI       ----  使用了TT_ARG使性能大为提高
      TE) (Cost=2 Card=25 Bytes=750)


统计信息
----------------------------------------------------------
      5  recursive calls
      0  db block gets
      9  consistent gets
      0  physical reads
      0  redo size
      970  bytes sent via SQL*Net to client
      523  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      25  rows processed

示例二：
（来自eygle的"使用物化视图进行翻页性能调整"一文）

系统症状：
出现大量unable to extend temp tablespace的错误，经查实是一条sql进行了大量的sort操作，大量消耗temp空间。

问题sql的特征：
来自于3个大表的全表扫描，而最终符合查询的记录却很少。

解决方案：
对数据进行预处理，即在符合查询要求的记录建立MV,性能得到极大提高。

注意：
1 对于经常更新的表不易创建MV,不然在更新MV时会有较大的系统消耗。
2 如果要在建立MV的表上进行大量的DML,需要考虑mv的增量同步日志，最好分批量进行，给MV的刷新留下一定空间。
可考虑如下代码：
begin
for i in 1..5 loop
--dml 语句；
if mod()=0 then
commit;
dbms_lock.sleep(300);
end loop;
end;