物化视图

视图 (VIEW) 可以看做是一种逻辑表，其数据来自于一张表或多张表，不占用物理存储空间。对视图的 DML 操作本质上是对其基表的操作。物化视图和普通视图一样，都是构建在一个查询语句之上的，只是物化视图会存储并刷新视图中的数据。

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

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

一、适用场景

软件 版本
操作系统 Redhat 7 及以上版本
DM 数据库 DM 8.0 及以上版本
CPU 架构 x86、ARM、龙芯、飞腾等国内外主流 CPU

二、关键参数介绍

2.1 数据填充时机

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

2.2 刷新模式

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

2.3 刷新时机

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

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

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

2.4 刷新依据

  • WITH PRIMARY KEY 基于主键

    • 只能基于单表
    • 必须含有 PRIMARY KEY 约束，选择列必须直接含有所有的 PRIMARY，KEY（UPPER(col_name) 的形式不可接受）
    • 不能含有对象类型

  • WITH ROWID 基于 rowid

    • 只能基于单表
    • 不能含有对象类型
    • 若同时使用 WITH ROWID 及快速刷新，则必须将 ROWID 提取出来，和其他列名一起，以别名的形式显示

三、物化视图的分类

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

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

通过查看系统视图 SYS.USER_MVIEWS 的 MVIEW_TYPE 列来了解所定义物化视图的分类。示例语句如下：

SELECT * FROM SYS.USER_MVIEWS;

输出结果：

image.png

四、创建物化视图

4.1 创建基于主键的物化视图

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

CREATE MATERIALIZED VIEW mv_employees REFRESH WITH PRIMARY KEY AS
SELECT * FROM dmhr.employee;

查询创建的物化视图。示例语句如下：

SELECT * FROM user_mviews t WHERE mview_name='MV_EMPLOYEES';

输出结果：

image.png

创建的物化视图在系统内部将其重命名为 MTAB$_MV_EMPLOYEE，通过 dba_segments 可以看出物化视图是占用一定的存储空间的，下图显示该物化视图包含 48 个块，有 3 个族组成。示例语句如下：

SELECT segment_name,bytes,blocks,extents FROM dba_segments
WHERE segment_name='MTAB$_MV_EMPLOYEES';

输出结果：

image.png

4.2 创建基于 rowid 的物化视图

若表上无主键，可使用基于 rowid 的方式创建物化视图。示例语句如下：

CREATE MATERIALIZED VIEW mv_employees_rowid REFRESH WITH ROWID AS
SELECT * FROM dmhr.employee;

通过系统表 user_mviews 查询物化视图信息，示例语句如下：

SELECT * FROM user_mviews t WHERE mview_name='MV_EMPLOYEES_ROWID';

查询物化视图数据，示例语句如下：

SELECT * FROM mv_employees;

输出结果：

image.png

五、物化视图日志

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

5.1 创建物化视图日志

一张表上只能创建一个物化视图日志。根据需求选择创建的类型，基于主键或者基于 ROWID。示例语句如下：

--创建基于主键的物化视图日志
CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH PRIMARY KEY;

--创建基于 ROWID 的物化视图日志
CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH ROWID;
注意

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

其中自动建立物化视图日志表定义如下：

CREATE TABLE dmhr.MLOG$_EMPLOYEE
(
    employee_id    INT,
    snaptime$$     DATETIME (6) NOT NULL,
    dmptype$$      CHAR (1) NOT NULL,
    sequence$$     BIGINT IDENTITY (1,1) NOT NULL
)
STORAGE (ON dmhr,  CLUSTERBTR);

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

ALTER MATERIALIZED VIEW mv_employees REFRESH FAST;

更新基表 dmhr.employee 的一条数据，比如増删改，日志表中会记录如下信息：

SELECT * FROM dmhr.MLOG$_EMPLOYEE;

输出结果：

物化视图日志

5.2 删除物化视图日志

示例语句如下：

DROP MATERIALIZED VIEW LOG ON dmhr.employee;

六、物化视图的刷新方式

  • COMPLETE，手动并全量刷新。命令执行后，所有修改都将同步到物化视图 mv_employees 中，对应的物化视图日志将被清空。示例语句如下：
REFRESH MATERIALIZED VIEW mv_employees COMPLETE;
  • FAST，快速刷新，必须事先建好物化视图日志。示例语句如下：
REFRESH MATERIALIZED VIEW mv_employees FAST;
  • FORCE，若存在物化视图日志则使用增量刷新，否则使用全量刷新。示例语句如下：
REFRESH MATERIALIZED VIEW mv_employees FORCE;
注意

一旦物化视图被刷新，master 表上的事务所做的修改会被自动提交。

七、物化视图的刷新时机

7.1 ON COMMIT

ON COMMIT 方式下，当用户提交对基表的操作后，自动刷新到物化视图上。

此方式下需要将变化提交到物化视图上，因而会延长对基表操作的提交时间。示例语句如下：

--删除物化视图
DROP MATERIALIZED VIEW mv_employees;

--创建物化视图
CREATE MATERIALIZED VIEW mv_employees REFRESH WITH PRIMARY KEY ON COMMIT AS
SELECT * FROM dmhr.employee;

若是已定义好的物化视图，仅需要修改刷新时机，示例语句如下：

ALTER MATERIALIZED VIEW mv_employees REFRESH ON COMMIT;

7.2 START WITH NEXT

定义物化视图，要求从当前时间开始工作，以后每隔 1 天执行一次。可是使用 START WITH…… NEXT 方式实现。示例语句如下：

CREATE MATERIALIZED VIEW mv1 REFRESH COMPLETE WITH PRIMARY KEY START WITH
SYSDATE NEXT SYSDATE + INTERVAL '1' DAY AS SELECT * FROM dmhr.employee;

八、复杂物化视图

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

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

8.1 存在 UNION 关键字时不支持快速刷新

示例语句如下：

--创建表并增加主键
DROP TABLE dmhr.emp01;
CREATE TABLE dmhr.emp01 AS SELECT * FROM dmhr.employee;
ALTER TABLE dmhr.emp01 ADD PRIMARY KEY(employee_id);

--创建物化视图日志
DROP MATERIALIZED VIEW LOG ON dmhr.employee;
CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dmhr.emp01 WITH PRIMARY KEY;

--创建物化视图
DROP MATERIALIZED VIEW mv1;
CREATE MATERIALIZED VIEW mv1 REFRESH WITH PRIMARY KEY FAST AS
SELECT * FROM dmhr.employee WHERE job_id=11
UNION
SELECT * FROM dmhr.emp01 WHERE job_id=31;

输出结果：

image.png

8.2 基于 ROWID 的快速刷新应包含所有 ROWID 列

示例语句如下：

DROP MATERIALIZED VIEW LOG ON dmhr.emp01;

CREATE MATERIALIZED VIEW LOG ON dmhr.emp01 WITH ROWID;
CREATE MATERIALIZED VIEW mv3 REFRESH WITH ROWID FAST
AS SELECT DISTINCT job_id FROM dmhr.emp01

输出结果：

image.png

8.3 仅包含 join 的物化视图快速刷新

外连接和自然连接目前不支持物化视图的快速刷新。包含连接的 SQL 语句中不能包括 group by 和 聚合函数。

表连接物化视图的快速刷新可以基于 PRIMARY KEY 或者 ROWID，但必须和刷新采用的方式一致，否则报如下错误。

  • 错误语句如下：
--删除物化视图日志
DROP MATERIALIZED VIEW LOG ON dmhr.employee;
DROP MATERIALIZED VIEW LOG ON dmhr.department;

--创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON dmhr.employee    WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW LOG ON dmhr.department  WITH PRIMARY KEY;

--创建仅包含 join 的物化视图
CREATE MATERIALIZED VIEW mv_join REFRESH FAST WITH ROWID AS
SELECT e.employee_id,
       e.employee_name,
       d.department_name,
       d.department_id,
       d.ROWID d_rowid
  FROM dmhr.employee e, dmhr.department d
 WHERE e.department_id = d.department_id

image.png

注意

连接中所有表的 rowid 列必须出现在 select 后面且 rowid 列必须使用别名。如果使用主键列，则所有表的主键列也必须都出现在 select 语句后面。

  • 正确语句如下：
--删除物化视图日志
DROP MATERIALIZED VIEW LOG ON dmhr.employee;
DROP MATERIALIZED VIEW LOG ON dmhr.department;

--创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON dmhr.employee   WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON dmhr.department WITH ROWID;

--创建仅包含 join 的物化视图
CREATE MATERIALIZED VIEW mv_join REFRESH WITH ROWID FAST AS
SELECT e.employee_id,
       e.employee_name,
       d.department_name,
       e.rowid e_rowid,
       d.rowid d_rowid
  FROM dmhr.employee e, dmhr.department d
 WHERE e.department_id = d.department_id

更新 1105 的部门名称，示例语句如下：

UPDATE dmhr.department SET department_name='技术支持部1' WHERE department_id=1105;

查询物化视图日志：

image.png

8.4 包含聚集函数的物化视图快速刷新

建立包含聚合函数并且支持快速刷新的物化视图，物化视图日志中必须包含那些在 select 语句中被引用的列。

下面的例子中就是 department_id 和 salary 两个列，使用 sequence 选项。

//删除物化视图日志
DROP MATERIALIZED VIEW LOG ON dmhr.employee;
DROP MATERIALIZED VIEW LOG ON dmhr.department;

//创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH ROWID,
SEQUENCE(department_id, salary);

//创建物化视图
CREATE MATERIALIZED VIEW mv_agg REFRESH WITH ROWID FAST AS
SELECT department_id,
         SUM (salary),
         COUNT (salary),
         COUNT (*)
    FROM  dmhr.employee
GROUP BY department_id;

查询物化视图，示例语句如下：

SELECT * FROM user_mviews t WHERE mview_name='mv_agg';

输出结果：

image.png

修改 1005 号员工的薪水并删除 1136 号员工的信息，均会影响 105 部门的最终统计信息。示例语句如下：

UPDATE dmhr.employee SET salary=1000 WHERE employee_id=1005;

支持快速刷新的物化视图日志如下：

image.png

存在分组聚合的物化视图支持快速刷新存在限制条件，下面列举两种容易出错的情况：

  • 错误语句一：
CREATE MATERIALIZED VIEW mv_agg REFRESH WITH PRIMARY KEY FAST AS
SELECT  department_id,
         SUM(salary),
         COUNT(*)
    FROM dmhr.employee

返回结果：分组物化视图定义中集函数没包含其他所需参数一致的集函数，不支持快速刷新。

image.png

  • 错误语句二：
CREATE MATERIALIZED VIEW mv_agg REFRESH WITH PRIMARY KEY FAST AS
SELECT  department_id,
         SUM(salary),
         COUNT(salary)
    FROM dmhr.employee
GROUP BY department_id;

返回结果：分组物化视图定义中查询项中没有 COUNT(*)，不支持快速刷新。

image.png

九、在物化视图上创建索引

创建索引，示例语句如下：

CREATE MATERIALIZED VIEW LOG ON dmhr.employee WITH PRIMARY KEY;
CREATE MATERIALIZED VIEW mv1 REFRESH FAST WITH PRIMARY KEY AS
SELECT * FROM dmhr.employee;

查询物化视图，示例语句如下：

SELECT * FROM mv1 WHERE employee_id=2002;

通过执行计划可以看出走的是全表扫描。

image.png

在物化视图上建立索引，加快查询效率，示例语句如下：

CREATE INDEX ind_mv1 ON MV1(employee_id);

查看创建的索引：

视图快速刷新

对比查询计划，走索引：

image.png

十、物化视图的维护

DM8 中物化视图对应的数据字典为 USER_MVIEW。物化视图日志记录基表的变化。

十一、参考文档

更多 SQL 语言使用说明，请参考《DM8_SQL 语言使用手册》，手册位于数据库安装路径 /dmdbms/doc 文件夹下。如有其他问题，请在社区内咨询。

微信扫码
分享文档
扫一扫
联系客服