Oracle中的物化视图是一种加速汇总数据查询效率的技术。
普通视图在做数据汇总时需要实时计算,物化视图可以通过提前计算并存储中间结果来加速查询过程。
通过提前计算并存储中间结果提高查询效率,可以使用手工方式来实现,比如创建中间表,然后定时更新数据。而物化视图是Oracle系统自带的,原理和手工创建中间表类似,起到加速查询的作用。
查询数据库中已经存在的物化视图,可以通过以下语句进行查询:
select T.* from DBA_MVIEWS T;
找到视图名称后,可以查看视图的DDL语句,对比传统视图看看有啥不同:
SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', 'MV_INPATIENT', 'XYHIS')
FROM DUAL;
CREATE MATERIALIZED VIEW MV_INPATIENT
USING NO INDEX
REFRESH FORCE ON COMMIT
AS
SELECT ID, MAIN_DIAGNOSE, INPATIENT_CODE,ROWID rd ,'1' flag
FROM PT_INPATIENT_CURE
UNION ALL
SELECT ID, MAIN_DIAGNOSE, INPATIENT_CODE,ROWID rd ,'2' flag
FROM PT_BABY_PATIENT;
在创建物化视图时,create view 前加了 materialized 关键字。以上例子中的物化视图的更新机制为基表执行 commit 时更新。
物化视图的创建语句有很多条件,如果什么条件都不加,会使用默认条件。
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;
其中更新条件 on demand 代表手动刷新(默认行为),on commit 代表事务提交时刷新。
其它关键字 build immediate 代表立即构建,build deferred 代表延迟构建(直到首次刷新时才构建)。refresh 刷新方式有:fast(增量刷新)、complete(全量刷新)、force(根据可用情况自动选择)。
也可以使用 start with 和 next 定义自动刷新时间和间隔。
如果对数据的一致性和即时性要求不高,可以选择手动刷新或定时刷新。如果要求物化视图查询结果与基表数据一致,则需要选择事务提交时刷新。
手动刷新语句为:
begin
dbms_mview.refresh('view_name', 'REFRESH_METHOD');
end;
--
-- REFRESH_METHOD 可以是以下选项
-- 'F':快速刷新(FAST)
-- 'C':完全刷新(COMPLETE)
-- '?F':如果可以快速刷新,则快速刷新,否则完全刷新
也可以不指定刷新策略:
begin
dbms_mview.refresh('XYHIS.MV_INPATIENT');
end;
物化视图可以使用 alter 语句修改:
ALTER MATERIALIZED VIEW view_name
REFRESH COMPLETE ON COMMIT;
如果对物化视图更新效率要求不高,以上语句基本就够用了。如果基表数据量很大或变化内容比较频繁,则需要考虑物化视图的更新效率。毕竟频繁全量更新中间表的代价比较大。
快速刷新需要跟踪基表内容的变化,使用快速刷新必须为基表创建物化视图日志(Materialized View Log)。日志将记录基表的更改,用于支持物化视图的增量刷新。
以下例子中物化视图 MV_INPATIENT 有两个基表 PT_BABY_PATIENT、PT_INPATIENT_CURE 。基于这两个基表创建物化视图日志:
CREATE MATERIALIZED VIEW LOG ON PT_BABY_PATIENT
WITH ROWID
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON PT_INPATIENT_CURE
WITH ROWID
INCLUDING NEW VALUES;
查询物化视图日志的语句为:
select t.* from dba_mview_logs t;
当删除物化视图时,并不会自动删除物化视图日志。物化视图日志可以单独删除,物化视图日志的存在只影响物化视图的刷新速度。
与之相关的删除语句为:
DROP MATERIALIZED VIEW LOG ON xyhis.PT_BABY_PATIENT ;
DROP MATERIALIZED VIEW LOG ON xyhis.PT_INPATIENT_CURE ;
DROP MATERIALIZED VIEW xyhis.mv_inpatient ;
当不创建物化视图日志时,无法快速刷新,即执行以下语句时会报错:
begin
dbms_mview.refresh('MV_INPATIENT','F');
end;
物化视图实际上是数据快照,它的原理图如下:
参考
https://oracle-base.com/articles/misc/materialized-views
https://blog.csdn.net/yangshangwei/article/details/53328605
https://stackoverflow.com/questions/11554886/how-to-refresh-materialized-view-in-oracle
https://www.cnblogs.com/tdskee/p/16152583.html
https://mp.weixin.qq.com/s/21U-f0vA4nKgBXsrqfDw-A
全文完。
评论