达梦数据库的视图、物化视图

qq_18821871

已于 2023-10-07 10:46:23 修改

阅读量2.3k

点赞数

文章标签：数据库

于 2023-10-07 10:21:25 首次发布

本文链接：https://blog.csdn.net/qq_18821871/article/details/133633747

版权

1.视图定义

视图是从一个或几个基表(或视图)导出的表，它是一个虚表，即数据字典中只存放视图的定义(由视图名和查询语句组成)，而不存放对应的数据，这些数据仍存放在原来的基表中。

当需要使用视图时，则执行其对应的查询语句，所导出的结果即为视图的数据。因此当基表

中的数据发生变化时，从视图中查询出的数据也随之改变了，视图就像一个窗口，透过它可

以看到数据库中用户感兴趣的数据和变化。由此可见，视图是关系数据库系统提供给用户以

多种角度观察数据库中数据的重要机制，体现了数据库本身最重要的特色和功能，它简化了

用户数据模型，提供了逻辑数据独立性，实现了数据共享和数据的安全保密。视图是数据库

技术中一个十分重要的功能。

2.视图的作用

视图是提供给用户以多种角度观察数据库中数据的重要机制。尽管在对视图作查询和更新时有各种限制，但只要用户对 DM_SQL 语言熟悉，合理使用视图对用户建立自己的管理信息系统会带来很多的好处和方便，归纳起来，主要有以下几点：

1．用户能通过不同的视图以多种角度观察同一数据

可针对不同需要建立相应视图，使他们从不同的需要来观察同一数据库中的数据。

2．简化了用户操作

由于视图是从用户的实际需要中抽取出来的虚表，因而从用户角度来观察这种数据库结构必然简单清晰。另外，由于复杂的条件查询已在视图定义中一次给定，用户再对该视图查询时也简单方便得多了。

3．为需要隐蔽的数据提供了自动安全保护

所谓“隐蔽的数据”是指通过某视图不可见的数据。由于对不同用户可定义不同的视图，

使需要隐蔽的数据不出现在不应该看到这些数据的用户视图上，从而由视图机制自动提供了

对机密数据的安全保密功能。

4．为重构数据库提供了一定程度的逻辑独立性

在建立调试和维护管理信息系统的过程中，由于用户需求的变化、信息量的增长等原因，

经常会出现数据库的结构发生变化，如增加新的基表，或在已建好的基表中增加新的列，或

需要将一个基表分解成两个子表等，这称为数据库重构。数据的逻辑独立性是指当数据库重

构时，对现有用户和用户程序不产生任何影响。

SQL> CREATE VIEW PURCHASING.V_VENDOR AS

SELECT VENDORID, ACCOUNTNO, NAME, ACTIVEFLAG, CREDIT

FROM PURCHASING.VENDOR

WHERE CREDIT = 1

操作已执行

已用时间: 16.379(毫秒). 执行号:2483.

SQL> SELECT * FROM PURCHASING.V_VENDOR;

行号 VENDORID ACCOUNTNO NAME ACTIVEFLAG CREDIT

---------- ----------- --------- ------------------------------ ---------- -----------

1 3 00 北京十月文艺出版社 1 1

2 4 00 人民邮电出版社 1 1

3 5 00 清华大学出版社 1 1

4 6 00 中华书局 1 1

5 7 00 广州出版社 1 1

6 8 00 上海出版社 1 1

7 9 00 21世纪出版社 1 1

8 10 00 外语教学与研究出版社 1 1

9 11 00 机械工业出版社 1 1

10 12 00 文学出版社 1 1

10 rows got

已用时间: 0.991(毫秒). 执行号:2484.

单表视图展示。

SQL> CREATE VIEW SALES.V_SALESPERSON_INFO AS

SELECT T1.SALESPERSONID, T2.TITLE, T3.NAME, T1.SALESLASTYEAR

FROM SALES.SALESPERSON T1, RESOURCES.EMPLOYEE T2, PERSON.PERSON T3

WHERE T1.EMPLOYEEID = T2.EMPLOYEEID AND T2.PERSONID = T3.PERSONID

2 3 4 5 操作已执行

已用时间: 17.092(毫秒). 执行号:2485.

SQL> SELECT * FROM SALES.V_SALESPERSON_INFO;

行号 SALESPERSONID TITLE NAME SALESLASTYEAR

---------- ------------- ------------ ------ -------------

1 1 销售代表郭艳 10.0000

2 2 销售代表孙丽 20.0000

已用时间: 1.145(毫秒). 执行号:2486.

视图也可以建立在多个基表之上

SQL> CREATE VIEW PRODUCTION.V_VENDOR_STATIS(VENDORID, PRODUCT_COUNT) AS

SELECT VENDORID, COUNT(PRODUCTID)

FROM PRODUCTION.PRODUCT_VENDOR

GROUP BY VENDORID

ORDER BY VENDORID

/2 3 4 5 6

操作已执行

已用时间: 17.814(毫秒). 执行号:2487.

SQL> SELECT * FROM PRODUCTION.V_VENDOR_STATIS;

行号 VENDORID PRODUCT_COUNT

---------- ----------- --------------------

1 5 1

2 6 2

3 7 1

4 8 1

5 9 1

6 10 1

7 11 1

7 rows got

已用时间: 1.730(毫秒). 执行号:2488.

用视图做统计，简化统计操作。

3.删除视图

SQL> DROP VIEW SALES.V_SALESPERSON_INFO;

操作已执行

已用时间: 13.989(毫秒). 执行号:2489.

SQL> DROP VIEW PURCHASING.V_VENDOR CASCADE;

操作已执行

已用时间: 13.008(毫秒). 执行号:2490.

当该视图对象被其他对象依赖时，用户在删除视图时必须带 CASCADE 参数，系统会将

依赖于该视图的其他数据库对象一并删除，以保证数据库的完整性。

4.视图的编译

一个视图依赖于其基表或视图，如果基表定义发生改变，如增删一列，或者视图的相关权限发生改变，可能导致视图无法使用。在这种情况下，可对视图重新编译，检查视图的合法性。

SQL> ALTER VIEW PRODUCTION.V_VENDOR_STATIS COMPILE;

操作已执行

已用时间: 13.078(毫秒). 执行号:2491.

重新编译视图

5.视图数据的更新

视图数据的更新包括插入(INSERT)、删除(DELETE)和修改(UPDATE)三类操作。由于视图是虚表，并没有实际存放数据，因此对视图的更新操作均要转换成对基表的操作。在SQL 语言中，对视图数据的更新语句与对基表数据的更新语句在格式与功能方面是一致的。

SQL> UPDATE PURCHASING.V_VENDOR SET ACTIVEFLAG = 0 WHERE NAME = '人民邮电出版社';

影响行数 1

已用时间: 1.127(毫秒). 执行号:2494.

SQL> COMMIT;

操作已执行

已用时间: 1.051(毫秒). 执行号:2495.

SQL> SELECT * FROM PURCHASING.V_VENDOR WHERE NAME = '人民邮电出版社';

行号 VENDORID ACCOUNTNO NAME ACTIVEFLAG CREDIT

---------- ----------- --------- --------------------- ---------- -----------

1 4 00 人民邮电出版社 0 1

已用时间: 1.126(毫秒). 执行号:2496.

SQL> SELECT * FROM PURCHASING.VENDOR;

行号 VENDORID ACCOUNTNO NAME ACTIVEFLAG WEBURL CREDIT

---------- ----------- --------- ------------------------------ ---------- ------ -----------

1 1 00 上海画报出版社 1 2

2 2 00 长江文艺出版社 1 2

3 3 00 北京十月文艺出版社 1 1

4 4 00 人民邮电出版社 0 1

5 5 00 清华大学出版社 1 1

6 6 00 中华书局 1 1

7 7 00 广州出版社 1 1

8 8 00 上海出版社 1 1

9 9 00 21世纪出版社 1 1

10 10 00 外语教学与研究出版社 1 1

11 11 00 机械工业出版社 1 1

行号 VENDORID ACCOUNTNO NAME ACTIVEFLAG WEBURL CREDIT

---------- ----------- --------- --------------- ---------- ------ -----------

12 12 00 文学出版社 1 1

12 rows got

已用时间: 0.568(毫秒). 执行号:2497.

基表和视图的数据都被更新。

SQL> INSERT INTO PURCHASING.V_VENDOR(ACCOUNTNO, NAME, ACTIVEFLAG, CREDIT) VALUES('00 ', '电子工业出版社', 1, 1);

影响行数 1

已用时间: 0.644(毫秒). 执行号:2498.

SQL> COMMIT;

操作已执行

已用时间: 0.920(毫秒). 执行号:2499.

SQL> SELECT * FROM PURCHASING.V_VENDOR;

行号 VENDORID ACCOUNTNO NAME ACTIVEFLAG CREDIT

---------- ----------- --------- ------------------------------ ---------- -----------

1 3 00 北京十月文艺出版社 1 1

2 4 00 人民邮电出版社 0 1

3 5 00 清华大学出版社 1 1

4 6 00 中华书局 1 1

5 7 00 广州出版社 1 1

6 8 00 上海出版社 1 1

7 9 00 21世纪出版社 1 1

8 10 00 外语教学与研究出版社 1 1

9 11 00 机械工业出版社 1 1

10 12 00 文学出版社 1 1

11 13 00 电子工业出版社 1 1

11 rows got

已用时间: 0.876(毫秒). 执行号:7300.

SQL> SELECT * FROM PURCHASING.VENDOR;

行号 VENDORID ACCOUNTNO NAME ACTIVEFLAG WEBURL CREDIT

---------- ----------- --------- ------------------------------ ---------- ------ -----------

1 1 00 上海画报出版社 1 2

2 2 00 长江文艺出版社 1 2

3 3 00 北京十月文艺出版社 1 1

4 4 00 人民邮电出版社 0 1

5 5 00 清华大学出版社 1 1

6 6 00 中华书局 1 1

7 7 00 广州出版社 1 1

8 8 00 上海出版社 1 1

9 9 00 21世纪出版社 1 1

10 10 00 外语教学与研究出版社 1 1

11 11 00 机械工业出版社 1 1

行号 VENDORID ACCOUNTNO NAME ACTIVEFLAG WEBURL CREDIT

---------- ----------- --------- --------------------- ---------- ------ -----------

12 12 00 文学出版社 1 1

13 13 00 电子工业出版社 1 NULL 1

13 rows got

已用时间: 0.527(毫秒). 执行号:7301.

往视图中插入一条数据，基表也会查出来。

SQL> DELETE FROM PURCHASING.V_VENDOR WHERE NAME = '中华书局';

DELETE FROM PURCHASING.V_VENDOR WHERE NAME = '中华书局';

[-6607]:违反引用约束[CONS134218816].

已用时间: 3.435(毫秒). 执行号:0.

对视图的操作会直接影响基表，该操作违反了基表的约束

SQL> UPDATE PRODUCTION.V_VENDOR_STATIS SET PRODUCT_COUNT = 3 WHERE VENDORID = 5;

UPDATE PRODUCTION.V_VENDOR_STATIS SET PRODUCT_COUNT = 3 WHERE VENDORID = 5;

第1 行附近出现错误[-2651]:试图更新只读视图[V_VENDOR_STATIS].

已用时间: 0.538(毫秒). 执行号:0.

基于基表中使用函数建立的视图，无法更新。

目前，不同的关系数据库管理系统产品对更新视图的可操作程度均有差异。DM 系统有这样的规定：

1. 如果视图建在单个基表或单个可更新视图上，且该视图包含了表中的全部聚集索引键，则该视图为可更新视图；

2. 如果视图由两个以上的基表导出时，则该视图不允许更新；

3. 如果视图列是集函数，或视图定义中的查询说明包含集合运算符、GROUP BY 子句HAVING 子句，则该视图不允许更新；

4. 在不允许更新视图之上建立的视图也不允许更新。

应该说明的是：只有当视图是可更新的时候，才可以选择 WITH CHECK OPTION 项。

6.物化视图定义

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

SQL> 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

2 3 4 5 6 操作已执行

已用时间: 35.695(毫秒). 执行号:7303.

SQL> SELECT * FROM PURCHASING.MV_VENDOR_EXCELLENT;

行号 VENDORID ACCOUNTNO NAME ACTIVEFLAG CREDIT

---------- ----------- --------- ------------------------------ ---------- -----------

1 3 00 北京十月文艺出版社 1 1

2 4 00 人民邮电出版社 0 1

3 5 00 清华大学出版社 1 1

4 6 00 中华书局 1 1

5 7 00 广州出版社 1 1

6 8 00 上海出版社 1 1

7 9 00 21世纪出版社 1 1

8 10 00 外语教学与研究出版社 1 1

9 11 00 机械工业出版社 1 1

10 12 00 文学出版社 1 1

11 13 00 电子工业出版社 1 1

11 rows got

已用时间: 0.920(毫秒). 执行号:7304.

查询物化视图。

对 VENDOR 表创建一个物化视图，名为 MV_VENDOR_EXCELLENT，保存信誉等级为 1 的供应商，列名有：VENDORID、ACCOUNTNO、NAME、ACTIVEFLAG、CREDIT。不

允许查询改写，依据 ROWID 刷新且刷新间隔为一天。

SQL> ALTER MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT REFRESH COMPLETE;

操作已执行

已用时间: 10.421(毫秒). 执行号:7305.

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

SQL> ALTER MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT ENABLE QUERY REWRITE;

操作已执行

已用时间: 6.414(毫秒). 执行号:7306.

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

SQL> DROP MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT;

操作已执行

已用时间: 51.206(毫秒). 执行号:7307.

删除物化视图

SQL> CREATE MATERIALIZED VIEW LOG ON PURCHASING.VENDOR WITH

ROWID(ACCOUNTNO,NAME,ACTIVEFLAG,WEBURL,CREDIT) PURGE START WITH SYSDATE + 5

REPEAT INTERVAL '1' DAY

2 3 4 操作已执行

已用时间: 47.036(毫秒). 执行号:7308.

建立物化视图日志，每天定时 PURGE

SQL> 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

2 3 4 5 6 操作已执行

已用时间: 31.536(毫秒). 执行号:7309.

建立可以快速刷新的物化视图

SQL> SELECT * FROM PURCHASING.MV_VENDOR_EXCELLENT;

行号 VENDORID ACCOUNTNO NAME ACTIVEFLAG CREDIT X

---------- ----------- --------- ------------------------------ ---------- ----------- ------------------

1 3 00 北京十月文艺出版社 1 1 AAAAAAAAAAAAAAAAAD

2 4 00 人民邮电出版社 0 1 AAAAAAAAAAAAAAAAAE

3 5 00 清华大学出版社 1 1 AAAAAAAAAAAAAAAAAF

4 6 00 中华书局 1 1 AAAAAAAAAAAAAAAAAG

5 7 00 广州出版社 1 1 AAAAAAAAAAAAAAAAAH

6 8 00 上海出版社 1 1 AAAAAAAAAAAAAAAAAI

7 9 00 21世纪出版社 1 1 AAAAAAAAAAAAAAAAAJ

8 10 00 外语教学与研究出版社 1 1 AAAAAAAAAAAAAAAAAK

9 11 00 机械工业出版社 1 1 AAAAAAAAAAAAAAAAAL

10 12 00 文学出版社 1 1 AAAAAAAAAAAAAAAAAM

11 13 00 电子工业出版社 1 1 AAAAAAAAAAAAAAAAAN

11 rows got

已用时间: 0.858(毫秒). 执行号:7310.

查询物化视图

SQL> REFRESH MATERIALIZED VIEW PURCHASING.MV_VENDOR_EXCELLENT FAST;

操作已执行

已用时间: 8.538(毫秒). 执行号:7311.

手动快速刷新物化视图，使用快速刷新前，必须先建好物化视图日志

SQL> DROP MATERIALIZED VIEW LOG ON PURCHASING.VENDOR;

操作已执行

已用时间: 36.821(毫秒). 执行号:7312.

删除物化视图日志。

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

据的修改只能通过刷新物化视图语句进行。

7.物化视图的一般限制

1.物化视图定义只能包含用户创建的表、视图和物化视图对象，且不能为外部表；

2．对化视图日志、物化视图只能进行查询和建索引，不支持插入、删除、更新、MERGE INTO 和 TRUNCATE；当设置 INI 参数 VIEW_OPT_FLAG=2 时，物化视图日志表支持插入、删除、更新和 TRUNCATE；

3．同一表上最多允许建立 127 个物化视图；

4．包含物化视图的普通视图及游标是不能更新的；

5．如果对某表进行了 TRUNCATE 操作，那么依赖于它的物化视图必须先进行一次完全刷新后才可以使用快速刷新；

6．如果对某表进行了快速装载操作，那么依赖于它的物化视图必须先进行一次完全刷新后才可以使用快速刷新；

7．如果对某表进行了与分区子表数据交换操作，那么依赖于它的物化视图必须先进行一次完全刷新后才可以使用快速刷新；

8．对于预建表物化视图定义中子查询的每一列，在预建表中必须有唯一的列与之对应，且列号、列名、列类型必须相同；

9．若在预建表上创建了预建表物化视图，则不能直接删除预建表，需要先删除物化视图，才能删除预建表。

8.物化视图的分类

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

1．SIMPLE：无 GROUP BY、无聚集函数、无连接操作、无远程数据库表、无视图；

2．AGGREGATE：仅包含有 GROUP BY 和聚集函数；

3．JOIN：仅包含有多表连接；

4．Sub-Query：仅包含有子查询；

5．SETS：包含 UNION ALL；

6．COMPLEX：除上述五种外的物化视图类型。

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

9.物化视图快速刷新通用约束

1．快速刷新物化视图要求每个基表都包含有物化视图日志，并且物化视图日志的创建时间不得晚于物化视图的最后刷新时间；

2．不能含有不确定性函数，如 SYSDATE 或 ROWNUM；

3．查询项不能含有分析函数；

4．查询不能含有 HAVING 子句；

5．不能包含 ANY、ALL 及 NOT EXISTS；

6．不能含有层次查询；

7．不能在多个站点含有相关表；

8．同一张表上最多允许建立 127 个快速刷新的物化视图；

9．不能含有除 UNION ALL 外的集合运算；

10．不能含有子查询；

11．只能基于普通表（视图，外部表，派生表等不支持）；

12．WITH PRIMAY KEY 时物化视图定义里如果是单表，则日志表里有 PK，如果是多表，则每张表的日志表里都有 PK；WITH ROWID 时物化视图里是单表，则日志表里必须有ROWID，如果是多表，则每张日志表里都有 ROWID；

13．对于 WITH ROWID 的快速刷新需要一一选择 ROWID 并给出别名；

14．WITH PRIMAY KEY 刷新时，物化视图定义中必须包含所有其基于的表的 PK 列；

15．如果日志定义中没有 WITH PRIMARY KEY 而扩展列又包含了，那么 DM 认为这个和建立日志时指定 WITH PRIMARY KEY 效果相同。也就是说，基于这个日志建立 WITH PK的快速刷新物化视图是允许的；

16．DM8 目前仅支持简单类型和部分连接物化视图的快速刷新。连接物化视图不支持的具体类型是外连接和自然连接；

17．连接物化视图不支持 GROUP BY 和聚集操作；

18．对于分组物化视图的快速刷新，有以下限制：

1)查询项中一定要有 count(*)；

2)集函数仅支持 count(*)、count、sum、avg、stddev、variance，不支持 max、min 等；

3)若某集函数出现在查询项中，则其依赖的集函数也必须出现

SQL> SELECT * FROM SYS.USER_MVIEWS;

行号 SCHID MVIEW_NAME QUERY

---------- ----------- ------------------- -------------------------------------------------------------------------------------------------------

QUERY_LEN REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD MVIEW_TYPE LAST_REFRESH_TYPE STALENESS LAST_REFRESH_DATE

-------------------- --------------- ------------ -------------- ---------- ----------------- --------- --------------------------

1 150995948 MV_VENDOR_EXCELLENT SELECT VENDORID,ACCOUNTNO,NAME,ACTIVEFLAG,CREDIT,ROWID AS "X" FROM PURCHASING.VENDOR WHERE CREDIT = 1

103 N DEMAND FAST SIMPLE FAST FRESH 2023-09-02 05:07:43.046000

已用时间: 2.049(毫秒). 执行号:7314.

用户可以通过SYS.USER_MVIEWS查看已有的物化视图以及物化视图的相关信息。

10.总结

普通视图就是一个虚拟表，不占内存。而物化视图是存在的，占内存。普通视图的查询实际上还是对sql的查询，并不能提高性能，只是看起来直观，简便。对于复杂的物化视图，更新的时机很重要，ON COMMIT因为只要基表发生COMMIT，物化视图就会更新；ON DEMAND可以为物化视图设置更新时间，安排在业务低峰期进行；

物化视图具有表一样的特征，所以可以像对表一样，我们可以为它创建索引，创建方法和对表一样。

https://eco.dameng.com