物化视图 | 达梦技术文档

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

在本章各例中，如不特别说明，以下例子用户均为建表者 SYSDBA。

7.1 物化视图的定义

语法格式

CREATE MATERIALIZED VIEW [<模式名>.]<物化视图名>[(<列名>{,<列名>})][<辅助表子句>|<预建表
子句>][<物化视图刷新选项>][<查询改写选项>]AS<查询说明>;
<辅助表子句>::= [BUILD IMMEDIATE|BUILD DEFERRED][<表空间子句>][<STORAGE子句>]
<表空间子句>::= 参见3.5.1.1 定义数据库基表
<STORAGE子句>::= 参见3.5.1.1 定义数据库基表
<预建表子句>::=  FOR <预建表表名> ON PREBUILT TABLE <精度设置子句>
<精度设置子句> ::= [WITH REDUCED PRECISION | WITHOUT REDUCED PRECISION] 
<物化视图刷新选项> ::= REFRESH <刷新选项> {<刷新选项>} | NEVER REFRESH
<刷新选项> ::= <刷新方法> | <刷新时机> | <刷新规则> | <完全刷新方式>
<刷新方法> ::= FAST | COMPLETE | FORCE
<刷新时机> ::= 
	ON DEMAND | ON COMMIT | 
	START WITH datetime_expr | NEXT datetime_expr |
	START WITH datetime_expr  NEXT datetime_expr
<刷新规则> ::= 
	WITH PRIMARY KEY | 
	WITH ROWID
<完全刷新方式> ::= 
	USING DEFAULT |
	USING TRUNCATE |
	USING DELETE
<查询改写选项>::= [DISABLE | ENABLE] QUERY REWRITE
<查询说明>::= <表查询> | <连接表>
<表查询>::= <子查询表达式> [ORDER BY子句]
<连接表>::= 请参考第4章 数据查询语句
<datetime_expr>::= SYSDATE [+<数值常量>]

参数

<模式名> 指明被创建的视图属于哪个模式，缺省为当前模式；
<物化视图名> 指明被创建的物化视图的名称；
<列名> 指明被创建的物化视图中列的名称；
[BUILD IMMEDIATE | BUILD DEFERRED] 指明 BUILD IMMEDIATE 为立即填充数据，默认为立即填充；BUILD DEFERRED 为延迟填充，使用这种方式要求第一次刷新必须为 COMPLETE 完全刷新；
<预建表表名 > 数据库中已存在的表叫做预建表；
ON PREBUILT TABLE 指明创建一个预建表物化视图，即指定一个已存在的表（预建表）作为物化视图的辅助表；
< 精度设置子句 > 标识预建表的每一列的精度是否可以比物化视图定义的子查询中各对应的列的精度低，目前 DM8 仅语法支持此项，实际功能未实现；
刷新方法

FAST

根据相关表上的数据更改记录进行增量刷新。普通 DML 操作生成的记录存在于物化视图日志。使用 FAST 刷新之前，必须先建好物化视图日志。
COMPLETE

通过执行物化视图的定义脚本进行完全刷新。
FORCE

默认选项。当快速刷新可用时采用快速刷新，否则采用完全刷新。

刷新时机

ON DEMAND

由用户通过 REFRESH 语法进行手动刷新。如果指定了 START WITH 和 NEXT 子句就没有必要指定 ON DEMAND。
ON COMMIT

在相关表上事务提交时进行快速刷新。刷新是由异步线程执行的，因此 COMMIT 执行结束后可能需要等待一段时间物化视图数据才是最新的。包含远程表的物化视图不支持 ON COMMIT 快速刷新。
START WITH datetime_expr | NEXT datetime_expr

START WITH 用于指定首次刷新物化视图的时间，NEXT 指定自动刷新的间隔；

如果省略 START WITH 则首次刷新时间为当前时间加上 NEXT 指定的间隔；

如果指定 START WITH 省略 NEXT 则物化视图只会刷新一次；

如果二者都未指定物化视图不会自动刷新。

刷新规则

WITH PRIMARY KEY

默认选项。
- 若显式指定刷新方法为 FAST，则必须含有 PRIMARY KEY 约束，此时选择列必须直接含有所有的 PRIMARY KEY（UPPER(col_name)的形式不可接受）；
WITH ROWID
- 如果使用 WITH ROWID 的同时使用快速刷新，则必须将 ROWID 提取出来，和其他列名一起，以别名的形式显示。

完全刷新方式
- USING DEFAULT
  
  默认选项。
  
  物化视图完全刷新时，根据 INI 参数 DEL_HP_OPT_FLAG 决定删除老数据使用的方式。
- USING TRUNCATE
  
  物化视图完全刷新时，使用 TRUNCATE 删除老数据。
- USING DELETE
  
  物化视图完全刷新时，使用 DELETE 删除老数据。
NEVER REFRESH 物化视图从不进行刷新。可以通过 ALTER MATERALIZED VIEW < 物化视图名 > FRESH 进行更改；
QUERY REWRITE 选项

ENABLE

允许物化视图用于查询改写。
DISABLE

禁止物化视图用于查询改写。

目前 DM8 仅语法支持查询改写选项，实际功能未实现；

< 子查询表达式 > 标识物化视图所基于的表的行和列。其语法遵照 SELECT 语句的语法规则；
定义查询中的 ORDER BY 子句仅在创建物化视图时使用，此后 ORDER BY 被忽略；
datetime_expr 只能是日期常量表达式，SYSDATE[+< 数值常量 >]或日期间隔。

图例

物化视图的定义

视图的定义-1

物化视图的定义-2.png

物化视图的定义-3.png

辅助表子句

辅助表子句.png

预建表子句

预建表子句.png

物化视图刷新选项

物化视图的定义-4.png

datetime_expr

语句功能

供 DBA 或该物化视图的拥有者且具有 CREATE MATERIALIZED VIEW 权限的用户定义物化视图。

使用说明

1．创建物化视图时，会产生两个字典对象：物化视图和物化视图表，后者用于存放真实的数据；

2．快速刷新物化视图的限制见本章第 8 节《物化视图的限制》；

3．由于受物化视图表的命名规则所限，物化视图名称长度必须小于 123 个字节。

权限

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

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

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

举例说明

例对 VENDOR 表创建一个物化视图，名为 MV_VENDOR_EXCELLENT，保存信誉等级为 1 的供应商，列名有：VENDORID、ACCOUNTNO、NAME、ACTIVEFLAG、CREDIT。不允许查询改写，依据 ROWID 刷新且刷新间隔为一天。

CREATE MATERIALIZED VIEW  PURCHASING.MV_VENDOR_EXCELLENT  
REFRESH WITH ROWID START WITH SYSDATE NEXT SYSDATE + 1 AS
SELECT  VENDORID, ACCOUNTNO, NAME, ACTIVEFLAG, CREDIT
FROM  PURCHASING.VENDOR
WHERE  CREDIT = 1;

如果使用 WITH ROWID 的同时，后面还要使用快速刷新，则此处的语句应写为：

//先创建好物化视图日志，创建步骤参考7.6节。
CREATE MATERIALIZED VIEW  PURCHASING.MV_VENDOR_EXCELLENT  
REFRESH FAST WITH ROWID START WITH SYSDATE NEXT SYSDATE + 1 AS
SELECT  VENDORID, ACCOUNTNO, NAME, ACTIVEFLAG, CREDIT,ROWID AS X
FROM  PURCHASING.VENDOR
WHERE  CREDIT = 1;

运行该语句后，DM 服务器将得到：1）物化视图：MV_VENDOR_EXCELLENT；2）以 MTAB$_为前缀的物化视图表：MTAB$_MV_VENDOR_EXCELLENT；3）定时刷新的物化视图触发器：MTRG_REFRESH_MVIEW_1670 （假定 MTAB$_MV_VENDOR_EXCELLENT 对象的 ID 是 1670）。

对该物化视图进行查询：

SELECT * FROM PURCHASING.MV_VENDOR_EXCELLENT;

查询结果如下：

VENDORID    ACCOUNTNO NAME                 ACTIVEFLAG CREDIT
----------- --------- -------------------- ---------- -----------
1           00        上海画报出版社         1          1
2           00        长江文艺出版社         1          1
3           00        北京十月文艺出版社     1          1
4           00        人民邮电出版社         1          1
5           00        清华大学出版社         1          1
6           00        中华书局              1          1
7           00        广州出版社            1          1
8           00        上海出版社            1          1
9           00        21世纪出版社          1          1
10          00        外语教学与研究出版社   1          1
11          00        机械工业出版社         1          1
12          00        文学出版社     	   1          1

7.2 物化视图的修改

语法格式

ALTER MATERIALIZED VIEW [<模式名>.]<物化视图名>[<物化视图刷新选项>][<查询改写选项>];
<物化视图刷新选项> ::= 参见7.1 物化视图的定义
<查询改写选项> ::= 参见7.1 物化视图的定义

参数

< 模式名 > 指明被创建的视图属于哪个模式，缺省为当前模式；
< 物化视图名 > 指明被创建的物化视图的名称。

图例

物化视图的修改

物化视图的修改.png

权限

使用者必须是该物化视图的拥有者或者拥有 ALTER ANY MATERIALIZED VIEW 系统权限。

举例说明

例 1 修改物化视图 MV_VENDOR_EXCELLENT，使之可以用于查询改写。

ALTER MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT ENABLE QUERY REWRITE;

例 2 修改物化视图 MV_VENDOR_EXCELLENT 为完全刷新。

ALTER MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT REFRESH COMPLETE;

7.3 物化视图的删除

语法格式

DROP MATERIALIZED VIEW [IF EXISTS] [<模式名>.]<物化视图名>;

参数

1．<模式名> 指明被删除视图所属的模式，缺省为当前模式；

2．<物化视图名> 指明被删除物化视图的名称。

图例

物化视图的删除

使用说明

1．删除不存在的视图会报错。若指定 IF EXISTS 关键字，删除不存在的视图，不会报错；

2．物化视图删除时会清除物化视图和物化视图表；

3．物化视图删除后，用户在其上的权限也均自动取消，以后系统中再建的同名物化视图，是与它毫无关系的物化视图；

4．用户不能直接删除物化视图表对象。

权限

使用者必须是物化视图的拥有者或者拥有 DROP ANY MATERIALIZED VIEW 系统权限。

举例说明

例删除物化视图 MV_VENDOR_EXCELLENT，可使用下面的语句：

DROP MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT;

7.4 物化视图的刷新

语法格式

REFRESH MATERIALIZED VIEW  [<模式名>.] <物化视图名> 
[ FAST | 
  COMPLETE [<完全刷新方式>] | 
  FORCE [<完全刷新方式>] ];
<完全刷新方式> ::= 参见7.1 物化视图的定义

图例

物化视图的刷新

物化视图的更新.png

权限

1．如果是基于物化视图日志的刷新，则使用者必须是物化视图日志的拥有者或者具有 SELECT ANY TABLE 系统权限；

2．使用者必须是物化视图的拥有者或者具有 SELECT ANY TABLE 系统权限。

使用说明

刷新方法的默认选项为 FORCE；
物化视图的刷新语句总是自动提交的，不能回滚。

举例说明

例采用 FAST 方法刷新物化视图 MV_VENDOR_EXCELLENT。

REFRESH MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT FAST;
//使用快速刷新前，必须先建好物化视图日志

7.5 物化视图允许的操作

对物化视图进行查询或建立索引时这两种操作都会转为对其物化视图表的处理。用户不能直接对物化视图及物化视图表进行插入、删除、更新和 TRUNCATE 操作，对物化视图数据的修改只能通过刷新物化视图语句进行。

7.6 物化视图日志的定义

物化视图的快速刷新依赖于基表上的物化视图日志，物化视图日志记录了基表的变化信息。

语法格式

CREATE MATERIALIZED VIEW  LOG ON [<模式名>.]<表名>
[<表空间子句>][<STORAGE子句>][<WITH子句>][<PURGE选项>];
<表空间子句>::=参见3.5.1.1节 定义数据库基表
<STORAGE子句>::=参见3.5.1.1节 定义数据库基表
<WITH 子句>::= WITH { PRIMARY KEY| ROWID | SEQUENCE | (<列名> {, <列名>})}
<PURGE选项>::= PURGE IMMEDIATE [ SYNCHRONOUS | ASYNCHRONOUS ]
      		| PURGE START WITH <datetime_expr> [ NEXT <datetime_expr> | REPEAT INTERVAL <interval_expr>]

参数

<模式名> 指明物化视图日志基表所属的模式，缺省为当前模式；
<表名> 指明创建日志的基表；
<WITH 子句> 基表中的哪些列将被包含到物化视图日志中，SEQUENCE 表示物化视图日志表中有 SEQUENCE$$唯一标识列，SEQUENCE 为默认选项；
<PURGE 选项> 指定每隔多长时间对物化视图日志中无用的记录进行一次清除。分两种情况：一是 IMMEDIATE 立即清除；二是 START WITH 定时清除。缺省是 PURGE IMMEDIATE。SYNCHRONOUS 为同步清除；ASYNCHRONOUS 为异步清除。ASYNCHRONOUS 和 SYNCHRONOUS 的区别是前者新开启一个事务来进行日志表的清理，后者是在同一个事务里。目前，ASYNCHRONOUS 仅语法支持，功能未实现；
<datetime_expr> 只能是日期常量表达式，SYSDATE[+< 数值常量 >]或日期间隔；
与物化视图可能依赖多个基表不同，物化视图日志只对应一个基表，因此物化视图日志是否使用行外大字段存储与基表保持一致。

图例

物化视图日志的定义

使用说明

在表 T 上创建物化视图日志后会生成：1）以 MLOG$_为前缀的名为 MLOG$_T 的日志表；2）以 MTRG$_为前缀的名为 MTRG$_T 的表级触发器；3）定时 purge 物化视图日志的触发器 MTRG_PURGE_MVLOG_1270（此处假定 MLOG$_T 对象的 ID 为 1270）。默认情况下，用户可以对 MLOG$_T 进行查询但是不能进行插入、删除和更新，触发器由系统维护，用户无法修改删除；设置 INI 参数 VIEW_OPT_FLAG=2 后，用户可以对 MLOG$_T 进行插入、删除、更新和 TRUNCATE；
由于物化视图日志表的命名规则所限，日志基表名称长度必须小于 123 个字符；
如果在物化视图 MV 上创建物化视图日志，系统会自动转为在物化视图的基表 MTAB$_MV 上创建物化视图日志，因此会生成名为 MLOG$_MTAB$_MV 的日志表和名为 MTRG$_MTAB$_MV 的表级触发器，且要求物化视图名称必须小于 116 个字符，其余限制和普通表一致。

权限

如果是物化视图日志基表的拥有者，使用者必须拥有 CREATE TABLE 系统权限；
如果物化视图日志基表是其它模式下的表，使用者必须拥有 CREATE ANY TABLE 系统权限，且物化视图日志的拥有者必须对<查询说明>中的每个表均具有 SELECT 权限或者具有 SELECT ANY TABLE 系统权限；
物化视图日志表仅支持基于的表为普通表、堆表和物化视图。

举例说明

例在 PURCHASING. VENDOR 上创建物化视图日志，每天定时 PURGE。

CREATE MATERIALIZED VIEW LOG ON PURCHASING.VENDOR WITH ROWID(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT) PURGE START WITH SYSDATE + 5  REPEAT INTERVAL '1' DAY;

注：间隔一天 PURGE 也可以写成 PURGE NEXT SYSDATE + 1。

7.7 物化视图日志的删除

语法格式

DROP MATERIALIZED VIEW LOG ON [<模式名>.]<表名>;

物化视图日志删除时会同时 DROP 掉日志表对象和触发器对象。另外，删除物化视图基表的同时，也会级联删除相应的物化视图日志。

图例

物化视图日志的删除

权限

使用者必须拥有删除表的权限。

7.8 物化视图的限制

7.8.1 物化视图的一般限制

物化视图定义只能包含用户创建的表、视图和物化视图对象，且不能为外部表；
对物化视图日志、物化视图只能进行查询和建索引，不支持插入、删除、更新、MERGE INTO 和 TRUNCATE；当设置 INI 参数 VIEW_OPT_FLAG=2 时，物化视图日志表支持插入、删除、更新和 TRUNCATE；
同一表上最多允许建立 127 个物化视图；
物化视图需要内建辅助表，命名规则为：MTAB$_物化视图名，因此，物化视图名称的最大长度为 122；
包含物化视图的普通视图及游标是不能更新的；
如果对某表进行了 TRUNCATE 操作，那么依赖于它的物化视图必须先进行一次完全刷新后才可以使用快速刷新；
如果对某表进行了快速装载操作，那么依赖于它的物化视图必须先进行一次完全刷新后才可以使用快速刷新；
如果对某表进行了与分区子表数据交换操作，那么依赖于它的物化视图必须先进行一次完全刷新后才可以使用快速刷新；
对于预建表物化视图定义中子查询的每一列，在预建表中必须有唯一的列与之对应，且列号、列名、列类型必须相同；
若在预建表上创建了预建表物化视图，则不能直接删除预建表，需要先删除物化视图，才能删除预建表。

7.8.2 物化视图的分类

依据物化视图定义中查询语句的不同分为以下六种。

SIMPLE：无 GROUP BY、无集函数、无连接操作、无远程数据库表、无视图；
AGGREGATE：仅包含有 GROUP BY 和集函数；
JOIN：仅包含有多表连接；
Sub-Query：仅包含有子查询；
SETS：包含 UNION ALL；
COMPLEX：除上述五种外的物化视图类型。

用户可以通过查看系统视图 SYS.USER_MVIEWS 的 MVIEW_TYPE 列来了解所定义物化视图的分类。

7.8.3 快速刷新通用约束

快速刷新物化视图要求每个基表都包含有物化视图日志，并且物化视图日志的创建时间不得晚于物化视图的最后刷新时间；
不能含有不确定性函数，如 SYSDATE 或 ROWNUM；
查询项不能含有分析函数；
查询不能含有 HAVING 子句；
不能包含 ANY、ALL 及 NOT EXISTS；
不能含有层次查询；
不能在多个站点含有相关表；
同一张表上最多允许建立 127 个快速刷新的物化视图；
不能含有除 UNION ALL 外的集合运算；
不能含有子查询；
只能基于普通表（视图，外部表，派生表等不支持）；
WITH PRIMARY KEY 时物化视图定义里如果是单表，则日志表里有 PK，如果是多表，则每张表的日志表里都有 PK；WITH ROWID 时物化视图里是单表，则日志表里必须有 ROWID，如果是多表，则每张日志表里都有 ROWID；
对于 WITH ROWID 的快速刷新需要一一选择 ROWID 并给出别名；
WITH PRIMARY KEY 刷新时，物化视图定义中必须包含所有其基于的表的 PK 列；
如果日志定义中没有 WITH PRIMARY KEY 而扩展列又包含了，那么 DM 认为这个和建立日志时指定 WITH PRIMARY KEY 效果相同。也就是说，基于这个日志建立 WITH PK 的快速刷新物化视图是允许的；
DM8 目前仅支持简单类型和部分连接物化视图的快速刷新。连接物化视图不支持的具体类型是外连接和自然连接；
连接物化视图不支持 GROUP BY 和聚集操作；
对于分组物化视图的快速刷新，有以下限制：1)查询项中一定要有 count(*)；2)集函数仅支持 count(*)、count、sum、avg、stddev、variance，不支持 max、min 等；3)若某集函数出现在查询项中，则其依赖的集函数也必须出现，具体见下表。

集函数	被依赖的集函数
sum(exp)	count(exp)，当 exp 是 not null 单列时，sum 不依赖于 count
avg(exp)	count(exp)、sum(exp)
stddev(exp)	count(exp)、sum(exp)、sum(exp*exp)
variance(exp)	count(exp)、sum(exp)、sum(exp*exp)

7.8.4 物化视图信息查看

用户可以通过系统视图 SYS.USER_MVIEWS 查看系统中所有物化视图的相关信息，视图定义如下：

表7.8.1 视图定义

列名	数据类型	备注
SCHID	INTEGER	模式 ID
MVIEW_NAME	VARCHAR(128)	物化视图名称
QUERY	TEXT	文本信息
QUERY_LEN	INTEGER	文本信息长度，单位：字节
REWRITE_ENABLED	VARCHAR(128)	是否可以被重写。Y 是，N 否
REFRESH_MODE	VARCHAR(128)	刷新模式：DEMAND,COMMIT
REFRESH_METHOD	VARCHAR(128)	刷新方法：COMPLETE,FORCE,FAST,NEVER
MVIEW_TYPE	VARCHAR(128)	快速刷新类型： SIMPLE：简单； AGGREGATE：聚合； JOIN：连接； Sub-Query：子查询； SETS：包含 UNION ALL； COMPLEX：不可快速刷新
LAST_REFRESH_TYPE	VARCHAR(128)	最后一次刷新的方法
STALENESS	VARCHAR(128)	物化视图状态： UNUSEABLE：物化视图不可用, 即从未刷新过； FRESH：物化视图数据是最新的； NEEDS_COMPILE：物化视图数据陈旧； COMPILE_ERROR：物化视图解析出错, 如基表不存在； NEEDS_FULL_REFRESH：物化视图数据陈旧, 需要一次全刷新； UNDEFINED：物化视图包含远程表状态不可知
LAST_REFRESH_DATE	DATETIME(6)	最后刷新开始的日期
LAST_REFRESH_END_DATE	DATETIME(6)	最后刷新结束的日期

7.9 物化视图的编译

一个物化视图依赖于其基表、如果基表定义发生改变，如修改或删除一列或多列，或者删除基表，可能导致物化视图无法刷新。另外，物化视图的快速刷新依赖于基表上的物化视图日志，如果日志表发生改变，如修改或删除一列，或者删除物化视图日志，可能导致物化视图无法刷新。对于预建表物化视图，当预建表发生改变时，也可能导致刷新失败。在这些情况下，可对物化视图重新编译，检查物化视图的合法性。

语法格式

ALTER MATERIALIZED VIEW [<模式名>.]<物化视图名> COMPILE;

参数

< 模式名 > 指明被编译视图所属的模式，缺省为当前模式；
< 物化视图名 > 指明被编译物化视图的名称；

图例

物化视图的编译

物化视图的编译.png

使用说明

对物化视图视图的定义重新进行分析和编译，如果编译出错，则报错。用户可以根据报错信息判断物化视图的依赖的基表、物化视图日志表或预建表是否发生改变或已被删除。

权限

使用者必须拥有 DBA 权限或是该物化视图的拥有者。

举例说明

例修改基表导致物化视图无法刷新，通过物化视图编译语句检查其合法性。

DROP TABLE IF EXISTS T1;
DROP MATERIALIZED VIEW IF EXISTS VIEW1;
CREATE TABLE T1(A INT, B VARCHAR);
INSERT INTO T1 VALUES(1,'1');
CREATE MATERIALIZED VIEW VIEW1 AS SELECT * from T1;
//修改基表列定义
ALTER TABLE T1 DROP B CASCADE;
//视图无法刷新
REFRESH MATERIALIZED VIEW VIEW1;
第1 行附近出现错误[-2207]:无法解析的成员访问表达式[T1.B].
//重新编译物化视图结果如下
ALTER MATERIALIZED VIEW VIEW1 COMPILE;
第1 行附近出现错误[-2207]:无法解析的成员访问表达式[T1.B].