转:oracle10g新特性——物化视图 1

在10g将查询重写并且引进了新的强大的调优建议者使管理物化视图变得容易多了。

    物化视图(Materialized Views MVs)，也被称为快照，现在已经被广泛应用了。MV将一个查询的结果存储在一个段中，并且当用户提交查询时返回查询结果，而不需要重新执行查询——如果查询会被执行多次（经常出现在数据仓库环境中），这就会非常有效。MV可以从基础表中完全刷新或通过使用快速刷新机制增量刷新。

假如你有如下定义的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'; 

    你如何知道使这个MV正常工作的所有必须对象都已经被创建呢？在10g之前，这一检测是通过包DBMS_MVIEW的存储过程EXPLAIN_MVIEW和EXPLAIN_REWRITE实现的。这些存储过程在10g还存在，它们的功能很简单——检测MV是否具备快速刷新能力和查询重新能力，但它们并不提供如何使这些能力有效的建议。相反，要求对于每个MV的结构都做检查是不切实际的。

    在10g中，有一个新的包DBMS_ADVISOR，它有一个存储过程TUNE_MVIEW使这项工作变得非常容易：你可以在调用这个包时输入一个输入参数，参数内容为创建MV的整个脚本。这个存储过程创建了一个建议者任务(Advisor Task)，它的名字会通常存储过程唯一的输出参数返回给用户。

这有一个例子。由于第一个参数是一个输出参数，所以你必须定义一个变量：

SQL> -- first define a variable to hold the OUT parameter 

SQL> var adv_name varchar2(20)
1 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;
Now you can find out the name of the Advisor from the variable.
SQL> print adv_name
ADV_NAME
----------------------- 

TASK_117 

    接下来，可以通过一个新视图DBA_TUNE_MVIEW从Advisor那获取到所提供的建议。在执行查询前记得先执行设置SET LONG 999999，因为这个视图中的这个字段是一个CLOB类型，而默认知会显示80个字符。

SQL> select script_type, statement
2 from dba_tune_mview
3 where task_name = 'TASK_117'
4 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语句。

     在实施的最后步骤，Advisor建议对MV的创建方式做一些修改。注意我们例子中的一个不同点：在MV上加了一个count(*)。由于我们定义这个MV是快速刷新，而count(*)又是必须的，所以Advisor修正了这一冗余。

    存储过程TUNE_MVIEW与EXPLAIN_MVIEW和EXPLAIN_REWRITE的不同之处不仅仅在于建议，它还能更容易鉴别出并提供一个效率更好的方式建立相同的MV。有时候Advisor能建议比使用一个单一的MV效率更高的的查询。

    你可能会问，如果一个经验丰富的DBA能找出MV创建脚本中的却些并且能自己调整它，那这些有什么用？当然，Advisor就是一个经验丰富、精力充沛、机器人似的的DBA，它能给出和人差不多的建议。但是和人有一个很大的不同：它可以随时工作而不需要假期和涨薪。这一好处可以使有经验的DBA从日常任务中解放出来，把这些工作留给普通的DBA去做。而把它们自己的经验发挥到更具战略意义的任务中。

    你也可以在嗲用TUNE_MVIEW时传入Advisor的名字，这样就不会使用系统自己生产的名字了。