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';