达梦物化视图的使用与管理

---

前言

物化视图是从一个或几个基表导出的表，同视图相比，它存储了导出表的真实数据。当基表中的数据发生变化时，物化视图所存储的数据将变得陈旧，可以通过手动刷新或自动刷新来对数据进行同步。

---

一、关于物化视图所需的权限

1．在自己模式下创建物化视图时，该语句的使用者必须被授予 CREATE MATERIALIZED VIEW 系统权限，且至少拥有 CREATE TABLE 或者 CREATE ANY TABLE 两个系统权限中的一个；

2．在其他用户模式下创建物化视图时，该语句的使用者必须具有 CREATE ANY MATERIALIZED VIEW 系统权限，且物化视图的拥有者必须拥有 CREATE TABLE 系统权限；

3．物化视图的拥有者必须对<查询语句>中的每个表均具有 SELECT 权限或者具有 SELECT ANY TABLE 系统权限。

初始化的一个用户使用物化视图至少需要以下权限：

create user TEST IDENTIFIED BY TEST123456;
GRANT RESOURCE,PUBLIC TO TEST;
GRANT CREATE MATERIALIZED VIEW to TEST;

二、创建手动刷新物化视图

1.测试环境构造

为了测试方便以下操作均在SYSDB下进行，在使用应用中，通常需要登录指定的业务用户下进行操作。

create user TEST IDENTIFIED BY TEST123456;

CREATE TABLE TEST.T1 (ID INT ,NAME VARCHAR(100));
INSERT INTO TEST.T1 VALUES(100,'AAA'),(200,'BBB'),(300,'CCC');
COMMIT;

GRANT RESOURCE,PUBLIC TO TEST;
grant CREATE MATERIALIZED VIEW to test;

select * from test.t1;

100	AAA
200	BBB
300	CCC

2.创建手动刷新物化视图

创建物化视图，不指定更新相关参数，默认为不更新。查询物化视图，可以看到与基表数据一致

create materialized view TEST.MV1 AS SELECT * FROM TEST.T1;

SELECT * FROM TEST.MV1;

100	AAA
200	BBB
300	CCC

往基表中插入新数据，再次查询，物化视图中数据没有发生变化。

INSERT INTO TEST.T1 VALUES(400,'DDD'),(500,'EEE');
COMMIT;

SELECT * FROM TEST.MV1;
100	AAA
200	BBB
300	CCC

需要通过手动刷新来实现更新。

数据的刷新方式有三种：
force，（默认）选择性更新，当快速刷新可用时采用快速刷新，否则采用完全刷新
complete ，完全更新，通过执行物化视图的定义脚本，重新定义物化视图
fast，快速更新，使用 FAST 刷新之前，必须先建好物化视图日志

refresh materialized view test.mv1;  --默认的force方式，默认参数可以不写
refresh materialized view test.mv1 complete ;  --完全更新
refresh materialized view test.mv1 fast;  --快速更新，没有创建日志表，如果用快速更新会报错。

#执行完刷新后，物化视图中的数据也被更新
SELECT * FROM TEST.MV1;

100	AAA
200	BBB
300	CCC
400	DDD
500	EEE

3.自动完全更新的物化视图

自动更新的语句与手动更新的差别是增加了更新相关参数“refresh complete on commit”，当基表触发commit操作，物化视图就会进行全量的刷新。

create materialized view test.mv3 refresh complete on commit as select * from test.t1;

SELECT * FROM TEST.MV3;

100	AAA
200	BBB
300	CCC
400	DDD
500	EEE

INSERT INTO TEST.T1 VALUES(600,'FFF');
commit;

SELECT * FROM TEST.MV3;

100	AAA
200	BBB
300	CCC
400	DDD
500	EEE
600	FFF

全量更新的物化视图如果是在业务量较为频繁的场景需要频繁的重建，会消耗大量的资源，物化视图还可以采用增量的方式快速更新。

4.自动快速更新的物化视图

使用快速更新的物化视图，基表必须具有主键
通过创建日志表，日志表中记录数据库的表，实现增量的数据刷新，可以提升物化视图的更新效率。

#给测试基表加个主键
alter table "TEST"."T1" add primary key("ID");

#创建物化视图日志表
create materialized view log on test.t1;

#创建快速更新物化视图
create materialized view test.mv4 refresh fast on commit as select * from test.t1;

SELECT * FROM TEST.MV4;
100	AAA
200	BBB
300	CCC
400	DDD
500	EEE
600	FFF

INSERT INTO TEST.T1 VALUES(700,'GGG');
commit;

SELECT * FROM TEST.MV4;
100	AAA
200	BBB
300	CCC
400	DDD
500	EEE
600	FFF
700 GGG

5.定时更新物化视图

通过在创建物化视图的更新策略中增加 START WITH … NEXT…语法,可以人工指定物化视图的更新频率。

START WITH 用于指定首次刷新物化视图的时间， NEXT 指定自动刷新的间隔；
如果省略 START WITH 则首次刷新时间为当前时间加上 NEXT 指定的间隔；
如果指定 START WITH 省略 NEXT 则物化视图只会刷新一次；
如果二者都未指定物化视图不会自动刷新

#以下语法为每隔2分钟更新一次。更新方式采用complete完全更新。
create materialized view test.mv5 refresh complete START WITH SYSDATE next sysdate + 2.0/24/60  as select * from test.t1;

INSERT INTO TEST.T1 VALUES(990,'JJJ');
COMMIT;
#间隔2分钟
select * from test.mv5;
100	AAA
200	BBB
300	CCC
400	DDD
500	EEE
600	FFF
700 GGG
990 JJJ



#更新间隔的NEXT写法
sysdate + 5.0/24/60           --间隔5分钟,
sysdate + 5.0/24              --间隔5小时,
add_days (sysdate, 5)         --间隔5天,
add_months (sysdate, 5)       --间隔5月,
add_months (sysdate, 5 * 12)  --间隔5年

三、物化视图的管理

可以通过系统视图 USER_MVIEWS 查看系统中所有物化视图的相关信息，包括更新方式以及最近的更新时间等。

通过DBMS_METADATA包可以提取物化视图的定义语句。通过定于语句可以查看物化视图的内部逻辑。

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','MV5','TEST')

---

四、总结

物化视图是数据库中非常重要的一种对象，在一些业务逻辑封装，报表优化等场景有非常广泛的应用，将一些实时性要求不高，并且业务逻辑复杂的报表SQL语句封装成物化视图，采用定时更新的方式，可以大大提升报表查询效率。合理使用既可以简化业务逻辑同时可以对数据库的整体性能有非常大的提升。

---

达梦技术社区地址：https://eco.dameng.com