达梦数据库物化视图介绍

概述

本文将介绍达梦数据库物化视图，给出其概念及相关创建、使用示例。

1.物化视图概念

物化视图 (MATERIALIZED VIEW) 是目标表在特定时间点上的一个副本，占用存储空间，即将查询出来的数据存储在数据库中。当所依赖的一个或多个基表的数据发生更新，必须启用刷新机制才能保证数据是最新的。

物化视图可以用于数据复制（Data Replication），也可用于数据仓库缓存结果集以此来提升复杂查询的性能。

1.1关键参数介绍

数据填充时机

• BUILD IMMEDIATE: 立即填充。
• BUILD DEFERRED: 延迟填充且第一次需要全量填充。

刷新模式

• FAST：根据相关表上的数据更改记录进行增量刷新。普通 DML 操作生成的记录存在于物化视图日志中。使用 FAST 刷新之前，必须先建好物化视图日志。
• COMPLETE：通过执行物化视图的定义脚本进行完全刷新。
• FORCE：默认选项。当快速刷新可用时采用快速刷新，否则采用完全刷新。

刷新时机

• ON COMMIT：相关表上有数据提交时进行刷新。刷新由异步线程执行，数据同步可能存在延迟。
• START WITH… NEXT：START WITH 指首次刷新的时间，省略 START WITH，首次刷新时间为当前时间。NEXT 指定刷新的时间间隔，省略 NEXT，则物化视图只刷新一次。

注意

START WITH 或者 NEXT 均不指定，不会**自动执行**物化视图刷新。

• ON DEMAND：用户可通过 REFRESH 语法手动刷新，不能和 START WITH 一起使用。
• NEVER REFRESH：物化视图从不刷新。

刷新依据

• WITH PRIMARY KEY 基于主键
  • 只能基于单表
  • 必须含有 PRIMARY KEY 约束，选择列必须直接含有所有的 PRIMARY，KEY（UPPER(col_name) 的形式不可接受）
  • 不能含有对象类型
• WITH ROWID 基于 rowid
  • 只能基于单表
  • 不能含有对象类型
  • 若同时使用 WITH ROWID 及快速刷新，则必须将 ROWID 提取出来，和其他列名一起，以别名的形式显示

1.2物化视图的分类

按物化视图的使用场景，主要分为如下五大类：

• SIMPLE：无 GROUP BY、无聚集函数、无连接操作
• AGGREGATE：仅包含 GROUP BY 和聚集函数
• JOIN：仅包含多表连接
• Sub-Query：仅包含子查询
• COMPLEX：除上述四种外的物化视图类型

2.创建物化视图和物化视图日志

2.1 创建物化视图

默认的物化视图类型，通过主键来标示行的变化，表上必须有主键。示例语句如下：

CREATE MATERIALIZED VIEW dmhr.mv_employees refresh with primary key as
select * from dmhr.employee;

通过user_mviewsb表查看创建的物化视图：

 通过dm管理工具查看创建的物化视图：

 

查询物化视图 dmhr.mv_employees数据：

SELECT * FROM dmhr.mv_employees;

查询结果如下：

 2.2创建物化视图日志

当基表上有 DML 操作时，系统将变化记录在日志表里，然后使用这些日志刷新到物化视图，这种刷新方式为快速刷新。通过快速刷新避免了全量刷新，也降低了同步数据的开销。

CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH PRIMARY KEY;

注意物化视图日志创建成功后，会自动建立物化视图日志表并在表上建立一个触发器。对应的触发器名称为 MTRG$_EMPLOYEE。

通过dm 管理工具查看物化视图日志表已经创建成功：

 

使用如下语法，将已创建的物化视图刷新方式由全量刷新改为快速刷新。示例语句如下：

ALTER MATERIALIZED VIEW dmhr.mv_employees REFRESH FAST;

接下来通过示例演示物化视图刷新操作：

更新dmhr.employee表员工为1001员工工资，然后刷新到其物化视图中。

查询dmhr.employee表的1001工号员工当前工资：

select EMPLOYEE_NAME,salary from dmhr.employee where EMPLOYEE_ID='1001';

查询结果如下：

更新dmhr.employee表1001工号员工工资，由30000更新至36000： 

update dmhr.employee set salary=36000  where EMPLOYEE_ID='1001';

再次查询dmhr.employee表1001工号员工工资，工资已更新至36000:

 接着查询dmhr.employee表的物化视图日志表：

SELECT * FROM dmhr.MLOG$_EMPLOYEE;

查询结果如下：

查询dmhr.employee表的物化视图： 

SELECT EMPLOYEE_NAME,salary FROM dmhr.mv_employees  where EMPLOYEE_ID='1001'; 

结果如下，1001工号员工工资没有更新至36000：

更新物化视图dmhr.mv_employees刷新模式为FORCE： 

REFRESH MATERIALIZED VIEW dmhr.mv_employees FORCE;

再次查询物化视图dmhr.mv_employees 1001号员工工资也已经更新至36000：

 再次将dmhr.employee表1001号员工工资更新至39000：

update dmhr.employee set salary=39000  where EMPLOYEE_ID='1001';

更新物化视图dmhr.mv_employees刷新模式为fast：

REFRESH MATERIALIZED VIEW dmhr.mv_employees fast;

查看物化视图dmhr.mv_employees1001号员工工资更新至39000：

更新物化视图刷新时机，当用户提交对基表的操作后，自动刷新到物化视图上。

ALTER MATERIALIZED VIEW dmhr.mv_employees REFRESH ON COMMIT;

 再次测试，将dmhr.employee表1001号员工工资更新至42000：

update dmhr.employee set salary=42000  where EMPLOYEE_ID='1001';

查看物化视图dmhr.mv_employees，显示1001号员工工资更新至42000：

 

3.复杂物化视图

支持快速刷新的物化视图，称为复杂物化视图。以下均为不能创建复杂物化视图场景，具体限制为：

• 不能含有不确定性函数，如 SYSDATE 或 ROWNUM。
• 不能含有大字段类型。
• 查询项不能含有分析函数。
• 查询不能含有 HAVING 子句。
• 不能包含 ANY、ALL 及 NOT EXISTS。
• 不能含有层次查询。
• 不能在多个站点含有相关表。
• 同一张表上最多允许建立 127 个快速刷新的物化视图。
• 不能含有 UNION，UNION ALL，MINUS 等集合运算。
• 不能含有子查询。
• 只能基于普通表（视图，外部表，派生表等不支持）。
• DM8 目前仅支持简单类型和部分连接物化视图的快速刷新，连接物化视图不支持的具体类型是外连接和自然连接。
• 连接物化视图不支持 GROUP BY 和聚集操作。

总结

首先，本文介绍了达梦数据库物化视图概念及其关键参数；
其次，介绍物化视图和物化视图日志创建及示例；
最后，介绍了复杂物化视图限制。

本文只是抛砖引玉，在实际应用中请参考达梦数据库官网文档物化视图相关介绍。

  关于达梦数据库更多学习内容，欢迎访问达梦社区：
https://eco.dameng.com