关于mv的一点见解

一般形式：

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 ptimizer=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 ptimizer=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;

 

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

转载于:http://blog.itpub.net/12742547/viewspace-218049/