oracle10g新特性——物化视图

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

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

[@more@]

在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的名字，这样就不会使用系统自己生产的名字了。

既然你知道了这些建议，你当然希望去实施它们了。一个方法就是将字段STATEMENT中内容取出，存到一个脚本文件中，并执行它。另外一个方法就是执行一个包里面的存储过程：

begin
dbms_advisor.create_file (
dbms_advisor.get_task_script ('TASK_117'),
'MVTUNE_OUTDIR',
'mvtune_script.sql'
);
end;
/

这一存储过程是假定你已经定义了一个目录对象的情况下调用的，如：

create directory mvtune_outdir as '/home/oracle/mvtune_outdir';

调用包dbms_advisor的这个存储过程会在目录/home/oracle/mvtune_outdir中生成一个名叫mvtune_script.sql的脚本文件。如果查看文件，它有如下内容：

Rem SQL Access Advisor: Version 10.1.0.1 - Production
Rem
Rem Username: ARUP
Rem Task: TASK_117
Rem Execution date:
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."HOTELS"
WITH ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."HOTELS"
ADD ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."RESERVATIONS"
WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUP.RESERVATIONS.CUST_NAME C1,
RUP.RESERVATIONS.RESV_ID C2, ARUP.HOTELS.CITY
C3, COUNT(*) M1 FROM ARUP.RESERVATIONS, ARUP.HOTELS WHERE
RUP.HOTELS.HOTEL_ID
= ARUP.RESERVATIONS.HOTEL_ID GROUP BY
RUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY;
whenever sqlerror EXIT SQL.SQLCODE
begin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;

/

这一文件包含了所有你需要实施的建议的内容，而不需要你手工去创建一个脚本。机器DBA又一次替你做了你需要做的工作。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13533/viewspace-1026301/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13533/viewspace-1026301/