DM 物化视图
物化视图是从一个或几个基表导出的表,同视图相比,它存储了导出表的真实数据。当基表中的数据发生变化时,物化视图所存储的数据将变得陈旧,用户可以通过手动刷新或自动刷新来对数据进行同步。
在本章各例中,如不特别说明,以下例子用户均为建表者SYSDBA。
7.1 物化视图的定义
语法格式
Copy
CREATE MATERIALIZED VIEW [<模式名>.]<物化视图名>[(<列名>{,<列名>})][BUILD IMMEDIATE|BUILD DEFERRED][<STORAGE子句>][<物化视图刷新选项>][<查询改写选项>]AS<查询说明>
<STORAGE子句>::=参见3.5.1.1节
<查询说明>::= <表查询> | <表连接>
<表查询>::=<子查询表达式>[ORDER BY子句]
<物化视图刷新选项> ::= REFRESH <刷新选项> {<刷新选项>} | NEVER REFRESH
<刷新选项> ::= [<刷新方法>][<刷新时机>][<刷新规则>]
<刷新方法> ::= FAST | COMPLETE | FORCE
<刷新时机> ::= [ON DEMAND | ON COMMIT] [START WITH datetime_expr | NEXT datetime_expr]
<刷新规则> ::= WITH PRIMARY KEY | WITH ROWID
<查询改写选项>::= [DISABLE | ENABLE] QUERY REWRITE
<datetime_expr>::= SYSDATE[+<数值常量>]
参数
-
<模式名> 指明被创建的视图属于哪个模式,缺省为当前模式;
-
<物化视图名> 指明被创建的物化视图的名称;
-
<列名> 指明被创建的物化视图中列的名称;
-
[BUILD IMMEDIATE|BUILD DEFERRED] 指明BUILD IMMEDIATE为立即填充数据,默认为立即填充;BUILD DEFERRED为延迟填充,使用这种方式要求第一次刷新必须为COMPLETE完全刷新。
-
<子查询表达式> 标识物化视图所基于的表的行和列。其语法遵照SELECT语句的语法规则;
-
<表连接> 请参看第四章表连接查询部分;
-
定义查询中的ORDER BY子句仅在创建物化视图时使用,此后ORDER BY被忽略;
-
刷新方法
-
FAST
根据相关表上的数据更改记录进行增量刷新。普通DML操作生成的记录存在于物化视图日志。使用FAST刷新之前,必须先建好物化视图日志。
-
COMPLETE
通过执行物化视图的定义脚本进行完全刷新。
-
FORCE
默认选项。当快速刷新可用时采用快速刷新,否则采用完全刷新。
-
刷新时机
-
ON DEMAND
由用户通过REFRESH语法进行手动刷新。如果指定了START WITH和NEXT子句就没有必要指定ON DEMAND。
-
ON COMMIT
在相关表上视图提交时进行快速刷新。刷新是由异步线程执行的,因此COMMIT执行结束后可能需要等待一段时间物化视图数据才是最新的。
约束:
含有对象类型的不支持;
包含远程表的不支持。
-
START WITH datetime_expr | NEXT datetime_expr
START WITH用于指定首次刷新物化视图的时间,NEXT指定自动刷新的间隔;
如果省略START WITH则首次刷新时间为当前时间加上NEXT指定的间隔;
如果指定START WITH省略NEXT则物化视图只会刷新一次;
如果二者都未指定物化视图不会自动刷新。
-
刷新规则
-
WITH PRIMARY KEY默认选项。
-
只能基于单表
-
必须含有PRIMARY KEY约束,选择列必须直接含有所有的PRIMARY KEY(UPPER(col_name)的形式不可接受)
-
不能含有对象类型
-
-
WITH ROWID
-
只能基于单表
-
不能含有对象类型
-
如果使用WITH ROWID的同时使用快速刷新,则必须将ROWID提取出来,和其他列名一起,以别名的形式显示
-
-
NEVER REFRESH 物化视图从不进行刷新。可以通过ALTER MATERALIZED VIEW <物化视图名> FRESH进行更改;
-
QUERY REWRITE选项
-
ENABLE
允许物化视图用于查询改写。
-
DISABLE
禁止物化视图用于查询改写。
目前DM8仅语法支持查询改写选项,实际功能未实现。
-
datetime_expr只能是日期常量表达式,SYSDATE[+<数值常量>]或日期间隔;
-
如果物化视图中包含大字段列,需要用户手动指定STORAGE(USING LONG ROW)的存储方式。
图例
物化视图的定义
![](https://img-blog.csdnimg.cn/8b46766e6b36409a92ba43610088bdb9.jpeg)
![](https://img-blog.csdnimg.cn/d363a438f6054dceb6a537d33c8558af.jpeg)
![](https://img-blog.csdnimg.cn/62d9cd402306439499fb9593e1cc6e19.jpeg)
datetime_expr
![](https://img-blog.csdnimg.cn/99e828aa27b14792bc65425bd48995dc.jpeg)
语句功能
供DBA或该物化视图的拥有者且具有CREATE MATERIALIZED VIEW权限的用户定义物化视图。
使用说明
-
创建物化视图时,会产生两个字典对象:物化视图和物化视图表,后者用于存放真实的数据;
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刷新且刷新间隔为一天。
Copy
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 的同时,后面还要使用快速刷新,则此处的语句应写为:
Copy
--先创建好物化视图,创建步骤参考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$_MV_VENDOR_EXCELLENT;3)定时刷新的物化视图触发器:MTRG_REFRESH_MVIEW_1670(假定MTAB$_MV_VENDOR_EXCELLENT对象的ID是1670)。
对该物化视图进行查询:
Copy
SELECT * FROM PURCHASING.MV_VENDOR_EXCELLENT;
查询结果见下表7.1.1。
表7.1.1
VENDORID | ACCOUNTNO | NAME | ACTIVEFLAG | CREDIT |
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 物化视图的修改
语法格式
Copy
ALTER MATERIALIZED VIEW [<模式名>.]<物化视图名>[<物化视图刷新选项>][<查询改写选项>]
参数
<物化视图刷新选项>和<查询改写选项>参见本章第1节“物化视图的定义”。
图例
物化视图的修改
![](https://img-blog.csdnimg.cn/cee80ba770c741f4a6ba4333bd72f1e1.jpeg)
权限
使用者必须是该物化视图的拥有者或者拥有ALTER ANY MATERIALIZED VIEW系统权限。
举例说明
例 修改物化视图MV_VENDOR_EXCELLENT,使之可以用于查询改写。
Copy
ALTER MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT ENABLE QUERY REWRITE;
例 修改物化视图MV_VENDOR_EXCELLENT为完全刷新。
Copy
ALTER MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT REFRESH COMPLETE;
7.3 物化视图的删除
语法格式
Copy
DROP MATERIALIZED VIEW [IF EXISTS] [<模式名>.]<物化视图名>;
参数
1.<模式名> 指明被删除视图所属的模式,缺省为当前模式;
2.<物化视图名> 指明被删除物化视图的名称;
图例
物化视图的删除
![](https://img-blog.csdnimg.cn/d4cd66c61dc04e66bc641db32ebd6d8c.png)
使用说明
1.删除不存在的视图会报错。若指定IF EXISTS关键字,删除不存在的视图,不会报错;
2.物化视图删除时会清除物化视图和物化视图表;
3.物化视图删除后,用户在其上的权限也均自动取消,以后系统中再建的同名物化视图,是与它毫无关系的物化视图;
4.用户不能直接删除物化视图表对象。
权限
使用者必须是物化视图的拥有者或者拥有DROP ANY MATERIALIZED VIEW系统权限。
举例说明
例 删除物化视图MV_VENDOR_EXCELLENT,可使用下面的语句:
Copy
DROP MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT;
7.4 物化视图的刷新
语法格式
Copy
REFRESH MATERIALIZED VIEW [<模式名>.] <物化视图名>
[FAST | COMPLETE | FORCE]
图例
物化视图的刷新
![](https://img-blog.csdnimg.cn/bfd68fca41294db7855e6cc5ac373e7d.jpeg)
权限
1.如果是基于物化视图日志的刷新,则使用者必须是物化视图日志的拥有者或者具有SELECT ANY TABLE系统权限;
2.使用者必须是物化视图的拥有者或者具有SELECT ANY TABLE系统权限。
使用说明
物化视图的刷新语句总是自动提交的,不能回滚。
举例说明
例 采用FAST方法刷新物化视图MV_VENDOR_EXCELLENT。
Copy
REFRESH MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT FAST;
--使用快速刷新前,必须先建好物化视图日志
7.5 物化视图允许的操作
对物化视图进行查询或建立索引时这两种操作都会转为对其物化视图表的处理。用户不能直接对物化视图及物化视图表进行插入、删除、刷新和TRUNCATE操作,对物化视图数据的修改只能通过刷新物化视图语句进行。
7.6 物化视图日志的定义
物化视图的快速刷新依赖于基表上的物化视图日志,物化视图日志记录了基表的变化信息。
语法格式
Copy
CREATE MATERIALIZED VIEW LOG ON [<模式名>.]<表名>
[<STORAGE子句>][<WITH子句>][<PURGE选项>]
<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[+<数值常量>]或日期间隔;
-
与物化视图可能依赖多个基表不同,物化视图日志只对应一个基表,因此物化视图日志是否使用行外大字段存储与基表保持一致。
图例
物化视图日志的定义
![](https://img-blog.csdnimg.cn/e235ed55e6624051a11bc5673d0489c3.jpeg)
使用说明
-
在表T上创建物化视图日志后会生成:1)MLOG$_T的日志表;2)MTRG$_T的表级触发器;3)定时purge物化视图日志的触发器:MTRG_PURGE_MVLOG_1270(假定MLOG$_T对象的ID是1270)。用户可以对MLOG$_T进行查询但是不能进行插入、删除和更新,触发器由系统维护,用户无法修改删除;
-
由于物化视图日志表的命名规则所限,日志基表名称长度必须小于123个字符;
-
如果在物化视图MV上创建物化视图日志,系统会自动转为在物化视图的基表MTAB$_MV上创建物化视图日志,因此会生成MLOG$_MTAB$_MV的日志表和MTRG$_MTAB$_MV的表级触发器,且要求物化视图名称必须小于116个字符,其余限制和普通表一致。
权限
-
如果是物化视图日志基表的拥有者,使用者必须拥有CREATE TABLE系统权限;
-
如果物化视图日志基表是其它模式下的表,使用者必须拥有CREATE ANY TABLE系统权限,且物化视图日志的拥有者必须对<查询说明>中的每个表均具有SELECT权限或者具有SELECT ANY TABLE系统权限;
-
物化视图日志表仅支持基于的表为普通表、堆表和物化视图。
举例说明
例 在PURCHASING. VENDOR上创建物化视图日志,每天定时PURGE。
Copy
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 物化视图日志的删除
语法格式
Copy
DROP MATERIALIZED VIEW LOG ON [<模式名>.]<表名>
物化视图日志删除时会同时DROP掉日志表对象和触发器对象。另外,删除物化视图基表的同时,也会级联删除相应的物化视图日志。
图例
物化视图日志的删除
![](https://img-blog.csdnimg.cn/81e62136139348b6919c9f827d687600.jpeg)
权限
使用者必须拥有删除表的权限。
7.8 物化视图的限制
7.8.1 物化视图的一般限制
-
物化视图定义只能包含用户创建的表、视图和物化视图对象,且不能为临时表和外部表;
-
对物化视图日志、物化视图只能进行查询和建索引,不支持插入、删除、更新、MERGE INTO和TRUNCATE;
-
同一表上最多允许建立127个物化视图;
-
包含物化视图的普通视图及游标是不能更新的;
-
如果对某表进行了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,UNION ALL,MINUS等集合运算;
-
不能含有子查询;
-
只能基于普通表(视图,外部表,派生表等不支持);
-
WITH PRIMAY KEY时物化视图定义里如果是单表,则日志表里有PK,如果是多表,则每张表的日志表里都有PK;WITH ROWID时物化视图里是单表,则日志表里必须有ROWID,如果是多表,则每张日志表里都有ROWID;
-
对于WITH ROWID的快速刷新需要一一选择ROWID并给出别名;
-
WITH PRIMAY KEY刷新时,物化视图定义中必须包含所有其基于的表的PK列;
-
如果日志定义中没有WITH PRIMARY KEY而扩展列又包含了,那么DM认为这个和建立日志时指定WITH PRIMARY KEY效果相同。也就是说,基于这个日志建立WITH PK的快速刷新物化视图是允许的;
-
DM8目前仅支持简单类型和部分连接物化视图的快速刷新。连接物化视图不支持的具体类型是外连接和自然连接;
-
连接物化视图不支持GROUP BY和聚集操作。
7.8.4 物化视图信息查看
用户可以通过系统视图SYS.USER_MVIEWS查看系统中所有物化视图的相关信息,视图定义如下:
列名 | 数据类型 | 备注 |
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) | 最后刷新的日期 |