day13_oracle物化视图

前面咱们讲过普通视图,只是方便咱们操作,但是它没有段产生,代表操作视图时I/O并没有提升。


物化视图可以把"热点数据"放在一个视图中,这个视图产生段,产生段的话,就可以分散I/O


物化视图可以看成一张特殊的表。


举例:使用物化视图

(1)
前端业务经常查询id,但是要对应着5张表,这样费5份I/O,找5张表。
你可以把这5张表按着条件放在一起,放在一张物化视图里。
物化视图占一个段,以后再查询的时候,CBO先看cost,如果查询物化视图cost比较小,直接就从物化视图中取值,就不用再从5张表中联合查询了。

(2)
某一个表非常大,可以分成很多物化视图

1、物化视图只是提高读动作(普通表可以insert,delete,update,select   物化视图只能select,如果你对物化视图做了修改,也没有作用,基表的数据不会改变。)
2、原始表和物化视图必须是周期性更新(CBO认为找物化视图优于找普通表,假如普通表更新了一部分数据,物化视图没有更新,那完蛋了,肯定找不到结果集了。)
面试问题:
  
(一)、一张20G的"大表"维护(如果减少表的I/O)

1、建立分区表
2、建立分区索引
3、按业务来瘦身
4、按业务做物化视图
5、在物化视图上建立索引
6、把表空间放在ASM上
7、加大缓存、提高命中率
8、把表和索引放在不同磁盘(表和索引放在不同表空间)
9、对大表大动作时,同时索引段也在修改,同时2份I/O。把索引停止,最后rebuild,减少CPU压力。
【空表64,索引64K        Inser:4W9条,大约6M,索引也增加到6M         truncate表,表64,索引64K】
10、针对大的索引考虑NOLOGGING选项

以上10小点分开来说,你套个场景,别像我这么总结。


(二)、一个SQL跑了20~30秒,问你正常不正常?

首先要按业务来区分

OLTP:不正常
      1、CPU计算量太大了
      2、把上面I/O的答案说说
      3、再加上SQL优化
      
OLAP:正常,如果跑一个分析动作,可能会以小时为单位。

物化视图(Meterialized View)提供了强大的功能,可以用于预先计算,并且保存表连接或者表聚集等耗时比较多的操作的结果,
这样在执行查询的时候,就可以避免这些耗时的操作,从而快速的得到结果。


     
1)使用物化视图的作用是为了提高查询的性能

2)当基表发生变化的时候,物化视图也应该发生变化

3)物化视图实际上是一张物理表,所以需要占用存储空间,所以才能分散I/O

4)物化视图对应用是透明的,增加或者删除物化视图中的数据,基表中的数据不会变化,所以不会影响应用中SQL语句的正确性和有效性



1、refresh [fast|complete|force] 视图刷新的方式:

FAST:

增量刷新,假设前一次刷新的时间为t1,那么使用FAST模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据。
为了记录这种变化,建立增量刷新物化视图的时候还需要一个物化视图日志表。




上次刷新10点,在11点,我再刷新的时候,只刷新10点--11点之间的变化 。
假如表小,就10条记录,没有影响。假如有10亿条,就影响大大的了。



COMPLETE:

全部刷新。相当于重新执行一次创建物化视图的查询语句。

FORCE:

这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。


2.MV数据刷新的时间:

ON DEMAND: 【服务器压力小】

在用户需要刷新的时候刷新,这里就要求用户自己动手去刷新数据了(也可以使用job定时刷新)。

ON COMMIT:【服务器压力大,但是同步效果好】

当主表中有数据提交的时候,立即刷新MV中的数据。



创建方式(Build Methods):

包括BUILD IMMEDIATE和BUILD DEFERRED两种。

BUILD IMMEDIATE是在创建物化视图的时候就生成数据,而BUILD DEFERRED则在创建时不生成数据,以后根据需要在生成数据。默认为BUILD IMMEDIATE。

查询重写(Query Rewrite):

包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。
分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,
如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。

举例:如果你开启了 查询重写功能。
当你查询某几张普通表时,CBO发现查询这几张表对应的物化视图,消耗的cost更少,自动就将你查询的请求指向物化视图。



3.物化视图日志
如果需要进行快速刷新,则需要建立物化视图日志。

-------------------------------------------

sqlplus / as sysdba

grant create materialized view,create view to scott;

例:
1.创建部门表,员工表
 create table dept2(deptno int,name varchar2(10));
  create table emp2(empno int, empname varchar2(10), deptno int);


 insert into dept2 values(1,'xinzu'); 
 insert into dept2 values(2,'jiuzu');

 insert into emp2 values(1,'zs',1);
 insert into emp2 values(2,'ls',1);
 insert into emp2 values(3,'ww',1);
 insert into emp2 values(4,'zl',1);
 insert into emp2 values(5,'cs',1);
 insert into emp2 values(6,'cp',1);

commit;

2.创建物化视图日志(rowid对应所有行)
 create materialized view log on emp2 with rowid;
 create materialized view log on dept2 with rowid;

3.创建物化视图(每30秒刷新一次)
 create materialized view wuhua
   refresh force on demand
   start with sysdate
   next sysdate + 1/24/60/2   
   with rowid
   as
   select a.empno,a.empname,b.deptno,b.name
   from emp2 a right join dept2 b
   on a.deptno=b.deptno;




 select segment_name,segment_type,bytes from user_segments where segment_name='WUHUA';-----物化视图产生段



select * from wuhua; 

4.
 update dept2 set dept2.name='jjj' where dept2.deptno=2;
 commit;

5.
select * from wuhua; 
(30秒后 wuhua表里也跟着变化了)

 DROP MATERIALIZED VIEW wuhua;


-----------------------------------------------
普通视图
create view v_tt as select owner,object_id from all_objects where object_id<1000;

 select segment_name,segment_type,bytes from user_segments where segment_name='V_TT';




posted @ 2016-03-21 21:29  暗夜小精灵~~  阅读(326)  评论(0编辑  收藏  举报