Oracle数据库中物化视图的原理剖析

物化视图 (MV)在一个段中存储查询结果，并且能够在提交查询时将结果返回给用户，从而不再需要重新执行查询 — 在查询要执行几次时（这在数据仓库环境中非常常见），这是一个很大的好处。物化视图可以利用一个快速刷新机制从基础表中全部或增量刷新。

假定您已经定义了一个物化视图，如下：

　　create materialized view mv_hotel_resv
　　refresh fast
　　enable query rewrite
　　as
　　select distinct city, resv_id, cust_name
　　from hotels h, reservations r 
　　where r.hotel_id = h.hotel_id';

　　

您如何才能知道已经为这个物化视图创建了其正常工作所必需的所有对象？在 Oracle 数据库 10g 之前，这是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 过程来判断的。这些过程（在 10g 中仍然提供）非常简要地说明一种特定的功能 — 如快速刷新功能或查询重写功能 — 可能用于上述的物化视图，但不提供如何实现这些功能的建议。相反，需要对每一个物化视图的结构进行目视检查，这是非常不实际的。

在 10g 中，新的 DBMS_ADVISOR 程序包中的一个名为 TUNE_MVIEW 的过程使得这项工作变得非常容易：您利用 IN 参数来调用程序包，这构造了物化视图创建脚本的全部内容。该过程创建一个顾问程序任务 (Advisor Task)，它拥有一个特定的名称，仅利用 OUT 参数就能够把这个名称传回给您。

下面是一个例子。因为第一个参数是一个 OUT 参数，所以您需要在 SQL*Plus 中定义一个变量来保存它。

　　
　　SQL> -- 首先定义一个变量来保存 OUT 参数
　　SQL> var adv_name varchar2(20)
　　SQL>　begin
　　2　dbms_advisor.tune_mview
　　　3　　 (
　　4　　　　:adv_name,
　　5　　　　'create materialized view mv_hotel_resv 
    refresh fast enable query rewrite as
　　select distinct city, resv_id, cust_name from hotels h, 
　　 　　reservations r where r.hotel_id = h.hotel_id');
　　6* end;
　

　

现在您可以在该变量中找出顾问程序的名称。

　　
　　SQL> print adv_name
　　
　　ADV_NAME
　　-----------------------
　　TASK_117

　　

接下来，通过查询一个新的 DBA_TUNE_MVIEW 来获取由这个顾问程序提供的建议。务必在运行该命令之前执行 SET LONG 999999，因为该视图中的列语句是一个 CLOB，默认情况下只显示 80 个字符。

　　
　　select script_type, statement 
　　from　 dba_tune_mview 
　　where　task_name = 'TASK_117' 
　　order　by script_type, action_id;

　　

下面是输出：

　　
　　SCRIPT_TYPE　　STATEMENT
　　-------------- -----------------------------------------------------------
　　IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID,
　　SEQUENCE ("HOTEL_ID","CITY")　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD
　　ROWID, SEQUENCE ("HOTEL_ID","CITY")　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH
　　ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
　　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS"
　　ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
　　INCLUDING NEW VALUES
　　
　　IMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV　 REFRESH FAST
　　WITH ROWID ENABLE QUERY REWRITE AS SELECT
　　ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
　　C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
　　ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
　　ARUP.RESERVATIONS.HOTEL_ID GROUP BY
　　ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
　　ARUP.HOTELS.CITY
　　
　　UNDO　　　　　 DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV

　　

SCRIPT_TYPE 列显示建议的性质。大多数行将要执行，因此名称为 IMPLEMENTATION。如果接受，则需按照由 ACTION_ID 列指出的特定顺序执行建议的操作。

如果您仔细查看这些自动生成的建议，那么您将注意到它们与您自己通过目视分析生成的建议是类似的。这些建议合乎逻辑；快速刷新的存在需要在拥有适当子句（如那些包含新值的子句）的基础表上有一个 MATERIALIZED VIEW LOG。STATEMENT 列甚至提供了实施这些建议的确切 SQL 语句。

在实施的最后一个步骤中，顾问程序建议改变创建物化视图的方式。注意我们的例子中的不同之处：将一个 count(*) 添加到了物化视图中。因为我们将这个物化视图定义为可快速刷新的，所以必须有 count(*)，以便顾问程序纠正遗漏。

TUNE_MVIEW 过程不仅在建议方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能，还为创建相同的物化视图指出了更容易和更高效的途径。有时，顾问程序可以实际推荐多个物化视图，以使查询更加高效。

您可能会问，如果任何一个经验丰富的 DBA 都能够找出 MV 创建脚本中缺了什么，然后自己纠正它，那这还有什么用？嗯，顾问程序正是用来完成这项工作的：它是一位经验丰富、高度自觉的自动数据库管理员，它可以生成能与人的建议相媲美的建议，但有一个非常重要的不同之处：它免费工作，并且不会要求休假或加薪。这一好处使高级 DBA 解放出来，将日常的工作交给较低级的 DBA，从而允许他们将其专业技能应用到更具有战略意义的目标上。

您还可以将顾问程序的名称作为值传递给 TUNE_MVIEW 过程中的参数，这将使用该名称而非系统生成的名称生成一个的顾问程序。

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

 

实体化视图概述

    Oracle的实体化视图提供了强大的功能，可以用在不同的环境中。在不同的环境中，实体化视图的作用也不相同。数据仓库中的实体化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。在数据仓库中，还经常使用查询重写（query rewrite）机制，这样不需要修改原有的查询语句，Oracle会自动选择合适的实体化视图进行查询，完全对应用透明。实体化视图和表一样可以直接进行查询。实体化视图可以基于分区表，实体化视图本身也可以分区。除了在数据仓库中使用，实体化视图还用于复制、移动计算等方面。实体化视图有很多方面和索引很相似：使用实体化视图的目的是为了提高查询性能；实体化视图对应用透明，增加和删除实体化视图不会影响应用程序中SQL语句的正确性和有效性；实体化视图需要占用存储空间；当基表发生变化时，实体化视图也应当刷新。

 

 

物化视图日志的维护

    物化视图日志经常会由于物化视图长时间没有刷新，或者基表的一次批量数据更改而变得很大，这会影响物化视图的刷新性能，因此对于这种情况需要对物化视图日志进行处理，降低物化视图日志表的高水位线。

  

Oracle的物化视图的快速刷新功能，主要是靠物化视图日志来实现的。

物化视图日志会记录下基表所有的增、删、改操作，而物化视图执行完快速刷新操作后，会从物化视图日志中将本物化视图刷新过且其他物化视图所不需要刷新的记录删除掉。如果其中一个物化视图一直不刷新，那么物化视图日志就会变得越来越大。

还有一种情况，比如表中插入了大量的数据，或者删除了大量的数据，或者将表中的某一列统一更新为一个值，这种操作都会在物化视图日志中产生大量的记录。

而物化视图日志的增大必然影响物化视图的刷新速度。一方面，物化视图在刷新的时候要扫描物化视图日志，另一方面，物化视图在刷新介绍后，也要清除物化视图日志中的记录，仍然要扫描物化视图日志，因此物化视图日志的大小直接会影响物化视图快速刷新的速度。更重要的是，物化视图日志的高水位一旦增长到一个很高的位置，即使以后物化视图日志中记录很少，甚至没有记录存在，物化视图在刷新的时候仍然需要较长的时间。

因此，在对于物化视图的基表进行操作时，应注意尽量更新需要更新的记录：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

已创建50674行。

SQL> CREATE MATERIALIZED VIEW LOG ON T;

实体化视图日志已创建。

用一个最简单的例子来说明什么叫做更新需要更新的记录。现在需要将T表中的NAME字段全部用大写来表示，最简单的写法：

SQL> UPDATE T SET NAME = UPPER(NAME);

已更新50674行。

SQL> SELECT COUNT(*) FROM MLOG$_T;

  COUNT(*)
----------
     50674

SQL> ROLLBACK;

回退已完成。

但是这种写法就会造成一些没有必要更新的记录也执行了更新操作，从而导致物化视图日志中记录了很多没有必要刷新的记录，这些记录不但影响物化视图日志的高水位线，而且会增加物化视图刷新的成本。

对于物化视图的基表，这个刷新则应该改写为：

SQL> UPDATE T SET NAME = UPPER(NAME) WHERE NAME != UPPER(NAME);

已更新34007行。

SQL> SELECT COUNT(*) FROM MLOG$_T;

  COUNT(*)
----------
     34007

采用这种方式就可以避免刷新不必要的列而使得物化视图日志变得很大。

不过有的时候大数据量的操作无可避免，或者物化视图日志本身已经变得很大，已经开始影响物化视图的刷新性能了，那么就只能通过维护物化视图日志表的方式来降低高水位线。

不应该对物化视图日志执行TRUNCATE TABLE操作。因为即使查询物化视图日志表中不存在记录，也无法确保在执行TRUNCATE TABLE操作之前，没有其他会话修改物化视图基表，从而导致新的记录插入物化视图日志中。

一旦发生物化视图日志记录被TRUNCATE的情况，就会导致物化视图和物化视图基表的数据不一致。例如：

SQL> CREATE MATERIALIZED VIEW MV_T REFRESH FAST AS SELECT * FROM T;

实体化视图已创建。

SQL> INSERT INTO T VALUES (60000, 'A');

已创建1行。

SQL> TRUNCATE TABLE MLOG$_T;

表被截断。

SQL> INSERT INTO T VALUES (60001, 'B');

已创建1行。

SQL> EXEC DBMS_MVIEW.REFRESH('MV_T')

PL/SQL过程已成功完成。

SQL> SELECT * FROM MV_T WHERE ID >= 60000;

        ID NAME
---------- ------------------------------

     60001 B

即使采用LOCK表的方式配合TRUNCATE，也无法避免并发的问题。

尝试在TRUNCATE之前LOCK物化视图日志表，很可能在TRUNCATE操作的时候失败：

SQL> LOCK TABLE MLOG$_T IN EXCLUSIVE MODE;

表已锁定。

会话1锁定物化视图日志表，这时会话2插入基表一条记录：

SQL> SET SQLP 'SQL2> '
SQL2> INSERT INTO T VALUES (60002, 'C');

会话1执行TRUNCATE语句：

SQL> TRUNCATE TABLE MLOG$_T;
              
第1行出现错误:
ORA-00054:资源正忙,但指定以NOWAIT方式获取资源

会话2成功插入记录：

已创建1行。

SQL2> SELECT ID FROM MLOG$_T;

       
----------
    60002

这是由于会话1执行TRUNCATE操作，会先发出一个COMMIT，从而释放了MLOG$_T上的锁，而这时会话2获得了MLOG$_T上的锁，并插入记录。由于会话2获得了物化视图日志上的锁，会话1尝试TRUNCATE就会失败。

如果尝试在基表上加锁，虽然可以避免基表的修改造成的物化视图日志改变，但是无法避免手工修改物化视图日志表的情况，虽然这种情况基本上不会发生。

因此处理物化视图高水位线最稳妥的方法还是使用MOVE的方式。