利用强制查询重写和新的强大的调整顾问程序 — 它们使您不再需要凭猜测进行工作 — 的引入在 g 中管理物化视图变得更加容易
物化视图 (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 rhotel_id = hhotel_id;
您如何才能知道已经为这个物化视图创建了其正常工作所必需的所有对象？在 Oracle 数据库 g 之前这是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 过程来判断的这些过程（在 g 中仍然提供）非常简要地说明一种特定的功能 — 如快速刷新功能或查询重写功能 — 可能用于上述的物化视图但不提供如何实现这些功能的建议相反需要对每一个物化视图的结构进行目视检查这是非常不实际的
在 g 中新的 DBMS_ADVISOR 程序包中的一个名为 TUNE_MVIEW 的过程使得这项工作变得非常容易您利用 IN 参数来调用程序包这构造了物化视图创建脚本的全部内容该过程创建一个顾问程序任务 (Advisor Task)它拥有一个特定的名称仅利用 OUT 参数就能够把这个名称传回给您
下面是一个例子因为第一个参数是一个 OUT 参数所以您需要在 SQL*Plus 中定义一个变量来保存它
SQL> 首先定义一个变量来保存 OUT 参数
SQL> var adv_name varchar()
SQL>begin
dbms_advisortune_mview
(
:adv_name
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 rhotel_id = hhotel_id);
* end;
现在您可以在该变量中找出顾问程序的名称
SQL> print adv_name
ADV_NAME
TASK_
接下来通过查询一个新的 DBA_TUNE_MVIEW 来获取由这个顾问程序提供的建议务必在运行该命令之前执行 SET LONG 因为该视图中的列语句是一个 CLOB默认情况下只显示 个字符
select script_type statement
from dba_tune_mview
wheretask_name = TASK_
orderby script_type action_id;
下面是输出
SCRIPT_TYPESTATEMENT
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON ARUPHOTELS WITH ROWID
SEQUENCE (HOTEL_IDCITY)INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON ARUPHOTELS ADD
ROWID SEQUENCE (HOTEL_IDCITY)INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON ARUPRESERVATIONS WITH
ROWID SEQUENCE (RESV_IDHOTEL_IDCUST_NAME)
INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON ARUPRESERVATIONS
ADD ROWID SEQUENCE (RESV_IDHOTEL_IDCUST_NAME)
INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW ARUPMV_HOTEL_RESV REFRESH FAST
WITH ROWID ENABLE QUERY REWRITE AS SELECT
ARUPRESERVATIONSCUST_NAME C ARUPRESERVATIONSRESV_ID
C ARUPHOTELSCITY C COUNT(*) M FROM ARUPRESERVATIONS
ARUPHOTELS WHERE ARUPHOTELSHOTEL_ID =
ARUPRESERVATIONSHOTEL_ID GROUP BY
ARUPRESERVATIONSCUST_NAME ARUPRESERVATIONSRESV_ID
ARUPHOTELSCITY
UNDO DROP MATERIALIZED VIEW ARUPMV_HOTEL_RESV
SCRIPT_TYPE 列显示建议的性质大多数行将要执行因此名称为 IMPLEMENTATION如果接受则需按照由 ACTION_ID 列指出的特定顺序执行建议的操作
如果您仔细查看这些自动生成的建议那么您将注意到它们与您自己通过目视分析生成的建议是类似的这些建议合乎逻辑快速刷新的存在需要在拥有适当子句（如那些包含新值的子句）的基础表上有一个 MATERIALIZED VIEW LOGSTATEMENT 列甚至提供了实施这些建议的确切 SQL 语句
在实施的最后一个步骤中顾问程序建议改变创建物化视图的方式注意我们的例子中的不同之处将一个 count(*) 添加到了物化视图中因为我们将这个物化视图定义为可快速刷新的所以必须有 count(*)以便顾问程序纠正遗漏
TUNE_MVIEW 过程不仅在建议方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能还为创建相同的物化视图指出了更容易和更高效的途径有时顾问程序可以实际推荐多个物化视图以使查询更加高效
您可能会问如果任何一个经验丰富的 DBA 都能够找出 MV 创建脚本中缺了什么然后自己纠正它那这还有什么用？嗯顾问程序正是用来完成这项工作的它是一位经验丰富高度自觉的自动数据库管理员它可以生成能与人的建议相媲美的建议但有一个非常重要的不同之处它免费工作并且不会要求休假或加薪这一好处使高级 DBA 解放出来将日常的工作交给较低级的 DBA从而允许他们将其专业技能应用到更具有战略意义的目标上
您还可以将顾问程序的名称作为值传递给 TUNE_MVIEW 过程中的参数这将使用该名称而非系统生成的名称生成一个的顾问程序
更容易的实施
既然您可以看到建议那么您可能想实施它们一种方式是选择列 STATEMENT假脱机到一个文件然后执行该脚本文件一种更容易的替代方法是调用附带的封装过程
begin
dbms_advisorcreate_file (
dbms_advisorget_task_script (TASK_)
MVTUNE_OUTDIR
mvtune_scriptsql
);
end;
/
该过程调用假定您已经定义了一个目录对象例如
create directory mvtune_outdir as /home/oracle/mvtune_outdir;
对 dbms_advisor 的调用将在 /home/oracle/mvtune_outdir 目录中创建一个名为 mvtune_scriptsql 的文件如果您查看一下这个文件您将看到
RemSQL Access Advisor:Version Production
Rem
RemUsername:ARUP
RemTask:TASK_
RemExecution date:
Rem
set feedback
set linesize
set trimspool on
set tab off
set pagesize
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON
ARUPHOTELS
WITH ROWID SEQUENCE(HOTEL_IDCITY)
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
ARUPHOTELS
ADD ROWID SEQUENCE(HOTEL_IDCITY)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
ARUPRESERVATIONS
WITH ROWID SEQUENCE(RESV_IDHOTEL_IDCUST_NAME)
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
ARUPRESERVATIONS
ADD ROWID SEQUENCE(RESV_IDHOTEL_IDCUST_NAME)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW ARUPMV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUPRESERVATIONSCUST_NAME C ARUPRESERVATIONSRESV_ID C ARUPHOTELSCITY
C COUNT(*) M FROM ARUPRESERVATIONS ARUPHOTELS WHERE ARUPHOTELSHOTEL_ID
= ARUPRESERVATIONSHOTEL_ID GROUP BY ARUPRESERVATIONSCUST_NAME ARUPRESERVATIONSRESV_ID
ARUPHOTELSCITY;
whenever sqlerror EXIT SQLSQLCODE
begin
dbms_advisormark_recommendation(TASK_IMPLEMENTED);
end;
/
这个文件包含了您实施建议所需的一切从而为您省去了相当大的手动创建文件的麻烦这个自动数据库管理员又一次能够为您完成工作
重写或退出！
至此您一定意识到了查询重写特性有多重要和多有用它显着地减少了 I/O 和处理并能够更快地返回结果
让我们基于上述例子假定一种情况用户执行以下查询
Select city sum(actual_rate)
from hotels h reservations r trans t
where tresv_id = rresv_id
and hhotel_id = rhotel_id
group by city;
执行状态显示以下内容
recursive calls
