物化视图

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

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

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

适用场景

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

关键参数介绍

数据填充时机

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

刷新模式

  • 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 提取出来,和其他列名一起,以别名的形式显示

物化视图的分类

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

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

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

SELECT * FROM SYS.USER_MVIEWS;

输出结果:

查询物化视图

创建物化视图

创建基于主键的物化视图

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

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

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

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

输出结果:

查询物化视图

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

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

输出结果:

查询物化视图

创建基于 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;

输出结果:

基于 rowid 的物化视图

物化视图日志

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

创建物化视图日志

一张表上只能创建一个物化视图日志。根据需求选择创建的类型,基于主键或者基于 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;

输出结果:

物化视图日志

删除物化视图日志

示例语句如下:

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 表上的事务所做的修改会被自动提交。

物化视图的刷新时机

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;

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 和聚集操作。

存在 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 COMPLETE AS
SELECT * FROM dmhr.employee WHERE job_id=11
UNION
SELECT * FROM dmhr.emp01 WHERE job_id=31;

输出结果:

错误举例1

基于 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

输出结果:

错误举例2

仅包含 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
注意

连接中所有表的 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

查询建立的物化视图 mv_join:

错误举例2

更新 1105 的部门名称,示例语句如下:

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

查询物化视图日志:

错误举例2

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

建立包含聚合函数并且支持快速刷新的物化视图,物化视图日志中必须包含那些在 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';

输出结果:

视图快速刷新

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

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

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

视图快速刷新

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

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

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

视图快速刷新

  • 错误语句二:
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(*),不支持快速刷新。

视图快速刷新

在物化视图上创建索引

创建索引,示例语句如下:

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;

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

视图快速刷新

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

CREATE INDEX ind_mv1 ON MV1(employee_id);

查看创建的索引:

视图快速刷新

对比查询计划,走索引:

视图快速刷新

物化视图的维护

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

参考文档

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

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