一 前言:
物化视图作为sql调优的高级技术,也是进行数据仓库调优的利器之一,总而言之这个技术还是比较炫的,也是比较少的能够在老板面前露一手的技术之一,很有必要了解一番;
二 概念:
视图是一个虚拟表(也可以认为是一条语句),基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询,可以将这个查询结果集存储到一个物化视图(也叫实体化视图)。
创建物化视图的语句
CREATE MATERIALIZED VIEW DATACHK.MV_B2B_DANWEI_R
AS
SELECT
"DWDM" DWDM,
"DWMC" DWMC,
"BYZD1" BYZD1,
"BYZD2" BYZD2,
"BYZD3" BYZD3,
"BYZD4" BYZD4,
"LastChanged" LastChanged
FROM DANWEI;
四 查询物化视图:SELECT * FROM DATACHK.MV_B2B_DANWEI_R; 查询刚才的物化视图
查看执行计划:
五 相关参数
在学习物化视图的时候,可能有的同学就会有疑问,如果基础表通过DML或者DDL语句进行修改时,物化视图可能会保护失效数据(如果现在对基础表新内容重新运行的话,得到的数据已经不再等于物化视图查询的结果集)。由于这个原因,在基础表发生了变更之后,必须对物化视图执行一次刷新动作,在创建的物化视图的时候,可以指定如何以及何时刷新的操作
1、何时刷新:定义当ORACLE基表发生变化时物化视图的刷新时间
- 根据需要(ON DEMAND):物化视图会在显式请求的情况下进行刷新(可以通过手工调用,也可以通过运行按照指定的时间间隔的任务)。这意味着从基础表修改带物化视图刷新这段时间内,物化视图中可能包含失效的数据。
- 在提交时(ON COMMIT):物化视图会在基础表修改所在的同一个事务里进行自动刷新,也就是说,物化视图总是包含最新的数据;(这种方式比较少用)
2、如何刷新:定义刷新数据的方式
- 完全刷新:容器表的所有内容都会被删除,并且所有的数据都会从基础表重新加载。所有的物化视图都支持这种方式,但是效率较慢,一般在一部分的数据被修改后才去使用这种方式; 语句:REFRESH COMPLETE
- 快速刷新:修改了的数据才会同步到容器表中。这种视图需要满足一定的条件,基础表上只有少数数据被变更后,用这种方式效率较快;
- 强制刷新:最初会尝试快速刷新。如果无法快速刷新,就执行一次完全刷新。(默认的方法)
- 永不刷新:物化视图永远不被刷新。特殊场景下使用;(如果这时候尝试创新,将会报错)
在实际的工作中一般根据实际的情况配置刷新的时间和方式
六 管理脚本
1、查看系统中所有物化视图的刷新时间
select refresh_method,refresh_mode,staleness,last_refresh_type,last_refresh_date from user_mviews;
七、总结
物化视图将数据转换存储在一张表中,视图对象在表里面,创建物化视图,需要注意基表数据更改后,物化视图的数据选择什么策略进行数据更新
1.何时更新(手动更新或规定时间点更新on demand、基表事务提交视图更新 on commit)
2.如何更新,选择更新方式
①完全更新,视图中数据删除,从基表中重新加载数据,效率低
②快速刷新,基表上修改的数据才会同步到视图上
③强制刷新,优先尝试快速刷新,不行就完全刷新(默认的方法)
④永不刷新,物化视图永远不会被刷新
八注意
1.物化视图具有表一样的特征,所以可以像对表一样,我们可以为它创建索引,创建方法和对表一样。
2.物化视图的删除:
虽然物化视图是和表一起管理的,但是在经常使用的PLSQL工具中,并不能用删除表的方式来删除(在表上右键选择‘drop’并不能删除物化视图),可以使用语句来实现:drop materialized view mv_name