达梦物化视图概念及简单示例

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

 

创建物化视图

语法：

CREATE MATERIALIZED VIEW [<模式名>.]<物化视图名> [{<列名>{,<列名>}}] [BUILD IMMEDIATE | BUILD DEFERRED] [<STORAGE子句>] [<物化视图刷新选项>] [<查询改写选项>] AS <查询说明>

 

其中：

<物化视图刷新选项> ::= 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：IMMEDIATE表示立即填充，默认为IMMEDIATE；DEFERRED表示延迟填充，使用延迟填充要求第一次刷新必须为COMPLETE方式；

后面的查询子句中，若使用了ORDER BY子句，则ORDER BY子句仅在创建物化视图时使用，此后ORDER BY被忽略；

 

刷新模式：

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

 

刷新时机：

1. ON COMMIT：在相关表上视图提交时进行快速刷新，刷新是由异步线程执行的，因此COMMIT执行结束后可能需要等待一段时间物化视图数据才是最新的；

约束：含有对象类型的不支持；包含远程表的不支持；

2. START WITH ... NEXT：START WITH用于指定首次刷新物化视图的时间，NEXT指定自动刷新的间隔；若省略START WITH则首次刷新时间为当前时间加上NEXT指定的间隔；若指定START WITH省略NEXT则物化视图只会刷新一次；若二者都未指定则物化视图不会自动刷新；START WITH...NEXT不能和ON COMMIT合用，会报语法错误；

3. ON DEMAND：用户通过REFRESH语法进行手动刷新，若指定了START WITH...NEXT就没有必要指定ON DEMAND；

4. NEVER REFRSH：表示物化视图从不进行刷新，可以通过ALTER MATERIALIZED VIEW <物化视图名> REFRESH进行更改。指定了NEVER REFRESH选项的物化视图也不能进行手动刷新；

 

刷新选项：

1. WITH PRIMARY KEY：默认选项。只能基于单表；必须含有PRIMRAY KEY约束，选择列必须直接含有所有的PRMARY KEY（UPPER(COL_NAME)的形式不可接受）；不能含有对象类型
2. WITH ROWID：只能基于单表；不能含有对象类型；若使用WITH ROWID的同时使用快速刷新，则必须将ROWID提取出来，和其它列名一起，以别名的形式显示；

 

QUERY REWRITE选项：

1. ENABLE：允许物化视图用于查询改写；
2. DISABLE：禁止物化视图用于查询改写；

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

 

datetime_expr只能是日期常量表达式、SYSDATE [+ 数值常量]或日期间隔；

 

若物化视图中包含大字段列，需要用户手动指定STORAGE (USING LONG ROW)的存储方式。

 

创建物化视图时，会产生两个字典对象：物化视图和物化视图表（命名规则MTAB$_物化视图名），后者用于存放真实的数据。

 

修改物化视图

语法：

ALTER MATERIALIZED VIEW [<模式名>.]<物化视图名> [物化视图刷新选项] [查询改写选项]

 

删除物化视图

语法：

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

物化视图删除时会清除物化视图和物化视图表，用户不能直接删除物化视图表。

 

更新物化视图

语法：

REFRESH MATERIALIZED VIEW [<模式名>.]<物化视图名>  [FAST | COMPLETE | COMPLETE]

如果是基于物化视图日志的更新，则使用者必须是物化视图日志的拥有者或具有SELECT ANY TABLE权限。

物化视图的更新语句总是自动提交的，不能回滚。

 

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

 

创建物化视图日志

语法：

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仅语法支持，功能未实现。

与物化视图可能依赖多个基表不同，物化视图日志只对应一个基表，因此物化视图日志是否使用行外大字段存储与基表保持一致。

 

在表上创建物化视图日志后，会生成一个名为MLOG$_表名的日志表和一个名为MTRG$_表名的表级触发器以及定时purge物化视图日志的触发器MTRG_PUGE_MVLOG_表对象ID。用户可以对日志表进行查询，但是不能进行插入、删除和更新，触发器由系统维护，用户无法修改删除。

若在物化视图MV上创建物化视图日志，系统会自动转为在物化视图表MTAB$MV上创建物化视图日志，因此会生成MLOG$_MTAB$_MV的日志表和MTRG$_MTAB$_MV的表级触发器。

物化视图日志表仅支持基于的表为普通表、堆表和物化视图。

 

删除物化视图日志

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

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

 

物化视图的限制：

1. 物化视图定义只能包含用户创建的表、视图和物化视图对象，且不能为临时表和外部表；
2. 对物化视图日志、物化视图只能进行查询和建索引，不支持插入、删除、更新、MERGE INTO和TRUNCATE；
3. 同一表上最多允许建立127个物化视图；
4. 包含物化视图的普通视图及游标是不能更新的；
5. 若对某个表进行了TRUNCATE操作，那么依赖于它的物化视图必须先进行一次完全更新后才可以使用快速刷新；
6. 若对某个表进行了快速装载，那么依赖于它的物化视图必须先进行一次完全更新后才可以使用快速刷新；
7. 若对某个表进行了与分区子表数据交换操作，那么依赖于它的物化视图必须先进行一次完全更新后才可以使用快速刷新；

 

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

1. SIMPLE：无GROUP BY、无聚集函数、无连接操作；
2. AGGREGATE：仅包含有GROUP BY和聚集函数；
3. JOIN：仅包含有多表连接；
4. SUB_QUERY：仅包含有子查询；
5. COMPLEX：除上述四种外的物化视图类型。

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

 

快速刷新的限制：

1. 快速刷新要求每个基表都包含有物化视图日志，且物化视图日志的创建时间不能晚于物化视图的最后刷新时间；
2. 不能含有不确定性函数，如SYSDATE或ROWNUM；
3. 不能含有大字段类型；
4. 查询项不能含有分析函数；
5. 查询不能含有HAVING子句；
6. 不能包含ANY、ALL以及NOT EXISTS；
7. 不能含有层次查询；
8. 不能再多个站点含有相关表；
9. 同一张表上最多允许建立127个快速刷新的物化视图；
10. 不能含有UNION、UNION ALL、MINUS等集合运算；
11. 不能含有子查询；
12. 只能基于普通表（视图、外部表、派生表等不支持）；
13. WITH PRIMARY KEY时物化视图定义里如果是单表，则日志里有PK，若是多表，则每张表的日志表里都有PK；WITH ROWID时物化视图里是单表，则日志表里必须有ROWID，若是多表，则每张日志表里都有ROWID；
14. 对于WITH ROWID的快速刷新，需要一一选择ROWID并给出别名；
15. WITH PRIMARY KEY刷新时，物化视图定义中必须包含所有其基于的表的PK列；
16. 若日志定义中没有WITH PRIMARY KEY而扩展列又包含了，那么DM认为这个和建立日志时指定WITH PRIMARY KEY效果相同。即基于这个日志建立WITH PK的快速刷新物化视图是允许的；
17. DM8目前仅支持简单类型和部分连接物化视图的快速刷新。连接物化视图不支持的具体类型是外连接和自然连接；
18. 连接物化视图不支持GROUP BY和聚集操作；

 

示例：

构造数据

CREATE TABLE TEST(A INT,B VARCHAR);

 

BEGIN

 FOR I IN 1..10 LOOP

  INSERT INTO TEST VALUES (I,'TEST'||I);

  COMMIT;

 END LOOP;

END;

 

CREATE MATERIALIZED VIEW MV AS SELECT * FROM TEST;

SELECT * FROM MV;

SELECT * FROM MTAB$_MV;

 

CREATE MATERIALIZED VIEW MV1(A,B) BUILD DEFERRED REFRESH ON DEMAND ENABLE QUERY REWRITE AS SELECT * FROM TEST;

SELECT * FROM MV1;

REFRESH MATERIALIZED VIEW MV1 FORCE;

SELECT * FROM MV1;

SELECT * FROM MTAB$_MV1;

 

CREATE MATERIALIZED VIEW LOG ON TEST WITH ROWID;

SELECT * FROM MLOG$_TEST;

CREATE MATERIALIZED VIEW MV2 REFRESH FAST ON COMMIT WITH ROWID ENABLE QUERY REWRITE AS SELECT ROWID AS R,* FROM TEST;

SELECT * FROM MV2;

UPDATE TEST SET B = 'HAHA' WHERE A = 1; COMMIT;

SELECT * FROM MV2;

 

ALTER TABLE TEST ADD CONSTRAINT PK PRIMARY KEY (A);

DROP MATERIALIZED VIEW LOG ON TEST;

CREATE MATERIALIZED VIEW LOG ON TEST WITH PRIMARY KEY PURGE IMMEDIATE;

CREATE MATERIALIZED VIEW MV3 REFRESH FAST ON COMMIT WITH PRIMARY KEY AS SELECT * FROM TEST;

SELECT * FROM MV3;

UPDATE TEST SET B = 'XIXI' WHERE A = 3; COMMIT;

SELECT * FROM MLOG$_TEST;

SELECT * FROM MV3;

REFRESH MATERIALIZED VIEW MV3 COMPLETE;

SELECT * FROM MV3;

创建REFRESH FAST ON COMMIT WITH PRIMARY KEY的物化视图，基表修改提交后，物化视图不会立即更新，前面文档里写的是有异步线程处理，但是等了很久也没更新。所以就手动刷新了。而且只能用COMPLETE方式刷新。搞不清楚是什么问题。