一、 简介
1. 物化视图是什么
逻辑上:物化视图相当于是个普通表,这可以通过其能被user_tables查出得到佐证,因此其上是可以建索引的。
物理上:物化视图是一种段,能从user_segment被查出,有自己的物理存储属性,也会占用数据库磁盘空间。
2. 物化视图的优点
- 保存复杂查询结果,提升sql响应时间
- 可定时同步其他数据库表数据(注意不支持实时同步)
- 提升安全性,仅展示部分数据给其他业务方
- 可使用物化视图定期归档旧数据
二、 物化视图分类与创建
通用语法如下
create materialized view [view_name]
build [immediate|deferred]
refresh [fast|complete|force]
[
on [commit|demand] |
start with (start_time) next (next_time)
]
[enable | disable] query rewrite
as select xxx from table_name[@dblink] where xxx;
1. 官方文档分类
根据官方文档,物化视图分为以下几类:
-
Primary Key Materialized View
CREATE MATERIALIZED VIEW hr.employees_mv1 WITH PRIMARY KEY
AS SELECT * FROM hr.employees@orc1.example.com;
-
ROWID Materialized View
CREATE MATERIALIZED VIEW oe.orders REFRESH WITH ROWID AS
SELECT * FROM oe.orders@orc1.example.com;
-
Object Materialized View(有自定义对象的物化视图)
CREATE TYPE oe.category_typ AS OBJECT
(category_name VARCHAR2(50),
category_description VARCHAR2(1000),
category_id NUMBER(2));
CREATE TABLE oe.categories_tab OF oe.category_typ(category_id PRIMARY KEY);
-- To create materialized views that can be fast refreshed based on the oe.categories_tab master table, create a materialized view log for this table.
-- The WITH OBJECT ID clause is required when you create a materialized view log on an object table.
CREATE MATERIALIZED VIEW LOG ON oe.categories_tab WITH OBJECT ID;
CREATE MATERIALIZED VIEW oe.categories_objmv OF oe.category_typ
REFRESH FAST
AS SELECT * FROM oe.categories_tab@orc1.example.com;
2. 根据刷新时机分类
当基表有更新后,何时刷新物化视图,分为:
- ON DEMAND(默认):“需要”时才进行刷新,需要用户自己手动刷新数据,或利用job定时刷新
- ON COMMIT:当基表中有数据提交的时候,立即刷新MV中的数据(实时刷新)。对基表性能影响较大,另外注意此类物化视图不能使用dblink访问远端对象。
3. 根据刷新机制分类
当基表有更新后,如果不是on commit类型,物化视图需要刷新后数据才能保持和基表一致,刷新方式如下:
- 全量刷新(COMPLETE):delete整个物化视图再重新插入,对性能和归档量影响较大
- 快速刷新(FAST):增量快速刷新,需要创建物化视图日志表,基表变化会记到物化视图日志表,等到刷新时间时将日志表中数据写入物化视图,同时delete日志表中数据。
- 强制刷新(FORCE):能用fast模式就用,否则使用complete方式
- 不刷新(NEVER)
三、 物化视图日志
物化视图日志基于基表创建,记录对基表的所有DML操作。无论基表被用于多少个物化视图,每个基表只可以有一个物化视图日志。只有创建了物化视图日志的基表才能使用fast增量刷新模式,增量刷新时会将物化视图日志中的dml记录应用到物化视图上,然后清空物化视图日志,重新开始记录。
drop基表后其上的物化视图日志会被连带删除,新建一个同名表物化视图并不会自动创建。
1. 分类
-
Primary Key:物化视图日志基于基表主键记录受DML影响行,要求基表有主键
-
Row ID:物化视图日志基于基表rowid记录受DML影响行,一般表无主键时用
-
Object ID: 物化视图日志基于自定义对象基表的object identifier记录受DML影响行,用于自定义对象基表
-
Combination:物化视图日志基于 以上三种模式的任意组合 记录基表受DML影响行,用于物化视图涉及多张基表并且基表包含以上三类的任意组合情况。
2. 创建与日志内容
create materialized view log on ilmtest.tmp0403 with rowid including new values;
不能建在sys下,否则会报错
物化视图日志名是自动生成的,格式是 mlog$_basetablename,例如 MLOG$_TMP0403
字段含义如下:
列名 | 字段含义 |
---|---|
第一列根据with后指定信息而定 | * 使用 with primary key,则第一列为主键列 * 使用 with rowid,则第一列为 m_row$$,用来存储发生变化的记录的rowid * 使用 with object id,则第一列为 sys_nc_oid$,用来记录每个变化对象的对象id |
SNAPTIME$$ | 表示刷新时间 |
DMLTYPE$$ | 表示dml操作类型,i表示insert,d表示delete,u表示update |
OLD_NEW$$ | 表示这个值是新值还是旧值。n(new) 表示新值,一般为delete操作;o(old)表示旧值,一般为Insert操作,u表示update操作。 |
CHANGE_VECTOR$$ | 修改矢量,用来表示被修改的是哪个或哪几个字段 |
XID$$ | 事务id |
测试详情参考
https://www.cnblogs.com/linjiqin/archive/2012/05/23/2514795.html
四、 常用物化视图创建例子
注意创建物化视图时要用直接物化视图属主用户建,否则sys用户也会报无权限。
1. 实时刷新物化视图 ON COMMIT
FAST增量刷新(包括实时刷新)必须要建物化视图日志,否则会报错。
create table ilmtest.tmp0403(a int);
-- 如果物化视图涉及多张基表,每张表都要建物化视图日志
create materialized view log on ilmtest.tmp0403 with rowid including new values;
-- ilmtest用户执行
CREATE MATERIALIZED VIEW ilmtest.tmp0403_mv
REFRESH FAST ON COMMIT with rowid
AS SELECT * from ilmtest.tmp0403;
这里特别注意一下commit语句的执行时间,测试的只是这么简单的表和插入语句,对于复杂基表和dml操作,on commit物化视图对基表性能影响可能非常大。
另外注意FAST增量刷新不允许truncate基表,否则刷新时会报错
2. 全量刷新物化视图
可以不建物化视图日志,需要定时刷新物化视图。
注意千万不要建REFRESH COMPLETE ON COMMIT 的物化视图(每次提交都全量刷新),虽然语法支持,但是性能会爆炸的。
CREATE MATERIALIZED VIEW ilmtest.complete_mv
REFRESH COMPLETE with rowid
AS SELECT * from ilmtest.tmp0403;
创建定时刷新job
-- ilmtest用户执行
declare
job_id number;
begin
DBMS_JOB.submit(
job =>job_id,
what => 'dbms_mview.refresh(''COMPLETE_MV'');',
next_date => sysdate,
interval => 'sysdate + 10/(60*24)'); -- 每10分钟刷新一次
COMMIT;
end;
/
其实也可以在建的时候就指定刷新时间和间隔
CREATE MATERIALIZED VIEW an_user_base_file_no_charge
REFRESH COMPLETE START WITH SYSDATE
NEXT sysdate + 10/(60*24)
AS
select distinct user_no
from cw_arrearage t
where (t.mon = dbms_tianjin.getLastMonth or t.mon = add_months(dbms_tianjin.getLastMonth, -1))
也可以手动刷新(可以用并行)
begin
dbms_mview.refresh(TAB=>'an_user_base_file_no_charge',METHOD=>'COMPLETE',PARALLELISM=>8);
end;
/
3. 增量按需刷新物化视图
通常用于定期同步远程DB数据,当然基表在本地也可以用,下面以远程同步为例:
-- 源库执行
create table ilmtest.tmp0403(a int);
-- 注意如果基表已有物化视图日志则不用再建
create materialized view log on ilmtest.tmp0403 with rowid including new values;
-- 目标库
-- 创建dblink
CREATE PUBLIC DATABASE LINK mylink CONNECT TO ilmtest IDENTIFIED BY "xxx" USING 'xxxx';
-- ilmtest用户执行
CREATE MATERIALIZED VIEW ilmtest.tmp0403_mv_link
refresh fast on demand with rowid
AS SELECT * from ilmtest.tmp0403@mylink;
创建定时刷新job
-- ilmtest用户执行
declare
job_id number;
begin
DBMS_JOB.submit(
job =>job_id,
what => 'dbms_mview.refresh(''TMP0403_MV_LINK'');',
next_date => sysdate,
interval => 'sysdate + 10/(60*24)'); -- 每10分钟刷新一次
COMMIT;
end;
/
也可以手动刷新,增量通常没必要用并行刷新
begin
dbms_mview.refresh(TAB=>'an_user_base_file_no_charge',METHOD=>'FAST');
end;
/
4. 强制刷新型物化视图
如果基表没有建物化视图日志,或者由于其他原因无法使用fast模式增量刷新,会自动使用complete模式全量刷新。
注意一下生产环境这种类型可能是个坑,看起来像增量刷新,实际上不符合条件用的全量刷新,导致系统负载和归档量都很大,而且可能不大容易发现。
-- ilmtest用户执行,如果ilmtest.tmp0403_2上没有物化视图日志,会使用全量刷新
CREATE MATERIALIZED VIEW ilmtest.tmp0403_mv_force
refresh force on demand with rowid
AS SELECT * from ilmtest.tmp0403_2;
创建定时刷新job
-- ilmtest用户执行
declare
job_id number;
begin
DBMS_JOB.submit(
job =>job_id,
what => 'dbms_mview.refresh(''TMP0403_MV_FORCE'');',
next_date => sysdate,
interval => 'sysdate + 10/(60*24)'); -- 每10分钟刷新一次
COMMIT;
end;
/
五、 管理与注意事项
1. 物化视图
查看相关信息
select * from dba_mviews;
更改物化视图字段长度
alter table 物化视图 modify 物化视图字段 varchar2(5);
2. 物化视图日志
查看相关信息
select * from DBA_MVIEW_LOGS;
删除物化视图日志
DROP MATERIALIZED VIEW LOG on base_table_name;
3. 注意事项
- 基表新增字段不会造成物化视图失效,但物化视图无法同步新增字段数据(包括给物化视图加对应字段和使用select *创建的物化视图都不行),需要drop物化视图后重建。可以通过在基表预留字段,在查询时使用 select xxx as 别名的方法在一定程度上避免这个问题
- 基表删除被物化视图用到的字段会造成物化视图失效,需重新编译或重建物化视图
- 删除物化视图日志会造成增量刷新不可用,导致退化为全量刷新或刷新时直接报错
- 物化视图上不会同步基表索引,需要自行按需创建
参考
https://www.linuxidc.com/Linux/2018-02/151112.htm
https://blog.csdn.net/hjm4702192/article/details/79880756