.Net程序员学用Oracle系列(23)：视图理论、物化视图

1、视图理论
2、物化视图
3、总结

我曾遇到一个项目的数据库中视图比表还要多很多（表和视图加起来上千个），几乎每个表都有对应的视图，而且有很多视图长得相似，比如有些视图关联的表一样，只是查询列表多或少了一两个字段。我敢断定，这就是因为一些水平低劣的开发人员看现有表或视图的数据不完全符合他的需要，就不假思索的拷贝过来改一下然后创建一个新的视图完事儿，久而久之，自然会出现很多相似甚至相同的视图。

在接手上述项目后，真正令我感到崩溃的是，查询语句往往涉及多个视图，且常常是相似的视图定义中又引用了另一个相似的视图，被引用的视图中又引用了另一个相似的视图……。总之就一个字——乱！有时候为了找与页面上字段对应的数据库字段得看完一堆的视图定义才能找到，因此那段时间我特别厌恶视图！并暗下决心：日后如果我能做主，绝不用视图。

后来我换了公司，新接触的项目数据库中基本没用视图。结果没过多久，我便发现代码中有很多相似甚至重复的查询语句，而且代码中的查询语句改起来也比较费劲，于是乎我对视图的主观意识发生了摇摆。我开始问自己：到底该不该用视图？如果用，要怎么用才合理？用了视图到底会降低性能还是会提升性能？

最后我通过阅读《Oracle Database Concepts》并反复实践和揣摩，终于对视图有所感悟。多学一点知识就少一点困惑，而我正是对视图理论方面有了大致了解后才解开对视图的各种困惑。本文第 1 节大部分内容是对官方手册的简化翻译，也夹杂了一些我个人的体会，英语好的读者也可以忽略本节直接阅读原文。

1、视图理论

视图是对一或多个表或其他视图中包含的数据的自定义呈现。视图将查询的输出视为一个表，因此可以认为视图就是存储的查询或虚拟表。视图中包含行和列，就像一个真实的表，但不包含数据本身。视图所引用的表被称为基表，视图总是呈现基表中最近的数据，视图可支持更新数据，所有对视图数据的更新都将被反映到视图的基表中，并受到基表的完整性约束和触发器的约束。

1.1、视图的存储

与表不同，视图不需要分配存储空间，视图也不包含实际数据。视图由查询定义，该查询从视图基表中提取或导出数据。因为视图是基于其他对象的，所以视图只需要在数据字典中存储定义视图的查询，而不需要额外的存储空间。

1.2、视图的作用

可通过视图以不同的形式来显示基表中的数据，而视图的强大之处在于它能够根据不同的用户需求来对基表中的数据进行不同形式的整合。视图的常见用途如下：

1、通过限制对基表中一组指定行或列的访问，来提供额外的安全控制。
2、视图允许通过表连接整合多个表中的相关行或列，构成一个新的数据集，从而达到隐藏数据复杂性的目的。
3、视图提供了从多个表中查询数据，而不必知道如何关联这些表的可能，因而简化了查询的 SQL 语句。
4、重命名视图的列或更改数据形式，不会影响视图所引用的基表，这样一来就能以不同的角度来呈现基表中的数据了。
5、保存复杂的查询，一个查询可能会对表数据进行复杂的计算，如果将这个查询保存为视图，那之后需求进行计算只需查询该视图即可。
6、表达不使用视图无法表达的查询，有时候用户需求过于复杂，几乎写不出来仅从基表中查询数据的单条 SQL 语句，如某些复杂的分组查询、联合查询等。

简单来说，合理运用视图，不仅可以提高数据的安全性，还可以少写代码，提升开发效率和程序的可维护性，也有利于在某些情况下灵活高效的控制数据的展现形式。

1.3、视图的工作机制

Oracle 将定义视图的语句以文本的形式存储在数据字典中。当用户在 SQL 语句中引用了视图时，Oracle 将完成以下三步动作：

1、将引用了视图的语句与视图的定义语句合并成一个语句。
2、在共享 SQL 区解析整合后的语句。
3、执行该语句。

如果共享 SQL 区中存在相似语句，Oracle 就不会重复解析，只有在共享 SQL 区中没有相似语句时，Oracle 才会为该语句创建新的共享 SQL 区。因此引用了视图的 SQL 语句也可能会节约内存进而提高查询性能。

1.4、视图的依赖性

定义视图的查询必须要引用其它对象（表、视图），换而言之，视图依赖于其所引用的对象而存在。Oracle 会自动地处理视图的依赖关系。例如，当用户删除视图的某个基表后再次创建它，Oracle 就会自动的去检查新的基表是否符合现有的视图定义，进而判断视图的有效性。在 PL/SQL Developer 中，所有 Oracle 认为无效的视图都会被打上红叉叉。

1.5、可更新的连接视图

连接视图是指在视图定义的查询的 FROM 字句中引用了多个表或视图的视图，而可更新的连接视图是指能够支持 UPDATE、INSERT 和 DELETE 操作的连接视图。数据字典视图ALL_UPDATABLE_COLUMNS、DBA_UPDATABLE_COLUMNS和USER_UPDATABLE_COLUMNS中包含了那些可更新的视图列信息。如果要确保视图可更新，那么视图定义中就不能包含以下语法结构：

1、集合运算符。
2、DISTINCT 运算符。
3、聚合函数或分析函数。
4、GROUP BY、ORDER BY、CONNECT BY 或 START WITH 子句。
5、SELECT 列表中的集合表达式。
6、SELECT 列表中的子查询。
7、JOIN 连接（也有例外情况）。

如果视图只引用了一个表，且包含该表的主键，一般就可以对视图进行 DML 操作。对于不支持 DML 操作的视图，如包含上述语法结构的连接视图，还可以使用 INSTEAD OF 触发器来更新数据，INSTEAD OF 触发器也被译作替代触发器。

语法：

CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column1 [,column2 ...]]}
ON [SCHEMA.]view_name
[FOR EACH ROW] -- 因为 INSTEAD OF 触发器只能在行级上触发，所以没有必要指定
[WHEN condition]
[DECLARE]
  -- 声明变量
BEGIN
  -- 触发器代码
END;

示例：

CREATE OR REPLACE TRIGGER trg_staff
INSTEAD OF INSERT OR UPDATE OR DELETE ON demo.v_staff
BEGIN
  IF INSERTING OR UPDATING THEN -- 不允许通过 v_staff 视图添加或修改数据
    RAISE_APPLICATION_ERROR(-20000,'禁止通过 v_staff 视图添加或修改数据！');
  END IF;
  
  IF DELETING THEN -- 当接收到删除 v_staff 视图数据的指令时，删除对应数据
    DELETE FROM demo.t_staff t WHERE t.staff_name=:OLD.staff_name;
  END IF;
END;

1.6、内联视图

内联视图不是模式对象，而是一个有别名的子查询，一般定义在 FROM 字句之后，可以在 SQL 语句中像使用普通视图一样的使用。内联视图是一种临时视图，不会存储到数据字典中，它和标准视图的主要区别是：不需要在执行 SQL 语句之前进行解析和创建共享 SQL 区。

2、物化视图

物化视图是包含查询结果的数据库对象，用于汇总、计算、复制及分发数据。物化视图能够预先计算并保存表连接或聚集等耗时较多的操作结果，这样，在执行查询时，就可以避免进行这些耗时的操作，从而快速的得到结果。物化视图是一种特殊的物理表，“物化”视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle 都实际上转换为视图 SQL 语句的查询，这样对整体查询性能的提高，并没有实质上的好处。

2.1、刷新物化视图

Oracle 在对主表进行 DML 操作之后，会通过刷新来维护物化视图中的数据（以确保物化视图和基表中的数据同步）。刷新模式有两种：ON DEMAND 和 ON COMMIT，而刷新方式有四种：FAST、COMPLETE、FORCE 和 NEVER。FAST 刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE 刷新对整个物化视图进行完全的刷新。如果选择FORCE 方式，Oracle 会在刷新前先判断下是否可以进行快速刷新，如果可以则采用 FAST 刷新，否则采用 COMPLETE 刷新。NEVER 指物化视图不进行任何刷新。

对于使用快速刷新方法的物化视图，物化视图日志或直接加载日志将保留对主表的更改记录。已经创建好的物化视图也还可以再修改它的刷新方式。物化视图还可以按要求定期刷新。

2.2、物化视图日志

物化视图日志是将更改同步到主表的模式对象。通过物化视图日志可以逐级刷新主表上定义的物化视图，此过程被称为增量或快速刷新。如果没有物化视图日志，Oracle 必须重新执行物化视图查询来刷新物化视图，这个过程称为完全刷新。通常，快速刷新比完全刷新需要更少的时间。

物化视图日志位于和主表相同模式中的主数据库中。每个主表上最多能定义一个物化视图日志。Oracle 可以根据物化视图日志对所有需要快速刷新的物化视图执行快速刷新。要快速刷新物化连接视图，必须为实例化视图引用的每个表创建一个物化视图日志。

创建物化视图日志：

示例一（创建支持主键实例化视图快速刷新的物化视图日志，并指定物理和存储特性）：

CREATE MATERIALIZED VIEW LOG ON demo.t_staff
  PCTFREE 5 -- 块保留的空间百分比
  TABLESPACE users
  STORAGE (INITIAL 10K NEXT 10K);

示例二（创建支持快速刷新 ROWID 物化视图和物化连接视图的物化视图日志）：

CREATE MATERIALIZED VIEW LOG ON demo.t_staff WITH PRIMARY KEY,ROWID;

示例三（创建支持快速刷新物化聚合视图的物化视图日志）：

CREATE MATERIALIZED VIEW LOG ON demo.t_staff
  WITH ROWID, SEQUENCE(staff_id)
  INCLUDING NEW VALUES;

更多创建物化视图日志的细节请参考：《Oracle Database SQL Reference: CREATE MATERIALIZED VIEW LOG》。修改物化视图日志的细节请参考：《Oracle Database SQL Reference: ALTER MATERIALIZED VIEW LOG》。

删除物化视图日志：语法结构和删除表相似，详细请参考《Oracle Database SQL Reference: DROP MATERIALIZED VIEW》。

DROP MATERIALIZED VIEW LOG ON demo.t_staff;

2.3、管理物化视图

创建物化视图：

语法：

CREATE MATERIALIZED VIEW
  [ schema. ]materialized_view
  [ column_alias [, column_alias]... ]
  [ OF [ schema. ]object_type ]
  [ (scoped_table_ref_constraint) ]
  { ON PREBUILT TABLE
    [ { WITH | WITHOUT } REDUCED PRECISION ]
  | physical_properties materialized_view_props
  }
  [ USING INDEX
    [ physical_attributes_clause
    | TABLESPACE tablespace
    ]
      [ physical_attributes_clause
      | TABLESPACE tablespace
      ]...
  | USING NO INDEX
  ]
  [ create_mv_refresh ]
  [ FOR UPDATE ]
  [ { DISABLE | ENABLE }
    QUERY REWRITE
  ]
  AS subquery ;

示例一：

CREATE MATERIALIZED VIEW LOG ON demo.t_staff
  WITH PRIMARY KEY
  INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_staff
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT
  AS SELECT t1.staff_id,t1.staff_name,DECODE(t1.gender,1,'男',0,'女','未知') gender
FROM demo.t_staff t1
WHERE t1.is_disabled=0;

示例二：

CREATE MATERIALIZED VIEW mv_staff2
AS SELECT t1.staff_id,t1.staff_name,DECODE(t1.gender,1,'男',0,'女','未知') gender,
          EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM t1.birthday) age
   FROM demo.t_staff t1
   WHERE t1.is_disabled=0;

更多创建物化视图的细节请参考：《Oracle Database SQL Reference: CREATE MATERIALIZED VIEW》。修改物化视图的细节请参考：《Oracle Database SQL Reference: ALTER MATERIALIZED VIEW》。

删除物化视图：语法结构和删除表相似，详细请参考《Oracle Database SQL Reference: DROP MATERIALIZED VIEW》。

DROP MATERIALIZED VIEW mv_staff2;

2.4、物化视图与索引

物化视图和索引都是为提高数据库性能而存在的，所以它们有一定的相似之处。列举如下：

1、它们都消耗存储空间。
2、当主表中的数据发生更改时，都需要刷新。
3、当它们用于查询时，都可以提高 SQL 执行的性能。
4、它们的存在对 SQL 应用程序和用户是透明的（能看到且可以直接操作的意思）。
5、可以对它们进行分区。

物化视图与索引不同点之一是：物化视图可以在 SQL 语句中直接访问，而索引是否生效取决于 Oracle 系统。

3、总结

本文主要讲述了普通视图的基本原理和物化视图基本用法，希望能对读者有所帮助。另外，由于我本人物化视图用的也不多，所以讲述的比较片面和浅显，需要进一步了解的读者可以再看看官网手册：《Oracle Database Concepts: Overview of Materialized Views》。

本文链接：http://www.cnblogs.com/hanzongze/p/oracle-view.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！

posted @ 2017-05-09 10:03 韩宗泽阅读(1219) 评论(0) 编辑收藏举报

刷新页面返回顶部

Ace Han

Think Bigger! Build Smarter!