ZT oracle10g新特性——物化视图

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又一次替你做了你需要做的工作。

重写还是报错

　　现在你可能已经认识到了查询重写特性是多么有用和重要。它能大大降低I/O和处理过程、返回结果更快。

　　还是假定以上的例子，用户执行一个下面的查询：

1 SQL> Select city, sum(actual_rate)
2 from hotels h, reservations r, trans t
3 where t.resv_id = r.resv_id
4 and h.hotel_id = r.hotel_id
5 group by city;
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)

　　请注意consistent gets的值是6——一个非常低的值。这一结果是基于这个查询已经基于从3张表创建的2个视图的查询重写。不是从表查询，而是从MV查询，一次消耗了更少的如磁盘IO和CPU的资源。

　　但是如果查询重写失败了会怎么样呢？可能会以为几个原因失败：如果初始化参数query_rewrite_integrity被设置为TRUSTED并且MV的状态为STALE，查询就不会被重写。你可以通过设置会话的参数来模拟这一过程。

SQL> alter session set query_rewrite_enabled = false;

　　执行这一命令后，查询计划显示是从3张表查询数据，而不是从MV：

0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)

　　请注意consistent gets的值：从6上升到了16。在真实环境中，这一结果恐怕是无法接受的。因为多出来的资源请求可能无法获得，而你就必须自己重写这一查询了。在那样的情况下，你就必须确保查询一定能被重写。

在Oracle 9i和以下版本中，可能只有一个方法实现：使查询重写失效而不是使基础表的访问失败。在10g中通过一个特殊的提示可以提供这样的机制：REWRITE_OR_ERROR。上面这个查询就可以这样写了：

1 SQL> select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
2 from hotels h, reservations r, trans t
3 where t.resv_id = r.resv_id
4 and h.hotel_id = r.hotel_id
5 group by city;
from hotels h, reservations r, trans t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite

　　这样就会产生一个ora-30393的错误信息。这个信息表示查询不能通过使用MV来重写，因此语句失败。这一错误保护可以防止查询长期运行后系统发生资源缺乏问题。但是还要注意一个潜在问题：如果一个查询成功了，而不是所有都成功了，这些MV就能被用于查询的重写。因此，如果MV_ACTUAL_SALES而不是MV_HOTL_RESV能被使用，查询将会重写，错误也不会产生。这种情况下，查询计划就如以下：

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=6 Bytes=156)
1 0 SORT (GROUP BY) (Cost=11 Card=6 Bytes=156)
2 1 HASH JOIN (Cost=10 Card=80 Bytes=2080)
3 2 MERGE JOIN (Cost=6 Card=80 Bytes=1520)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'HOTELS' (TABLE) (Cost=2 Card=8 Bytes=104)
5 4 INDEX (FULL SCAN) OF 'PK_HOTELS' (INDEX (UNIQUE)) (Cost=1 Card=8)
6 3 SORT (JOIN) (Cost=4 Card=80 Bytes=480)
7 6 TABLE ACCESS (FULL) OF 'RESERVATIONS' (TABLE) (Cost=3 Card=80 Bytes=480)
8 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=560)

　　这一查询使用了MV_ACTUAL_SALES而不是MV_HOTEL_RESV，这样，表HOTELS和RESERVATIONS就能够访问。这种情况下，特别使对后面两种表会做全表扫描的情况下，将会消耗更多的资源——在你创建MV和设计查询语句时要特别注意。

　　尽管你已经通过资源管理器（Resource Manager）控制资源使用，使用这一提示能防止在资源管理器被调用前查询被执行。资源管理器基于优化器的统计数据来降低资源的消耗，因此统计数据的有无将会影响这一过程。而“重写还是报错”这一特性将会不管有误统计数据都会阻值表的访问。

　　更佳的查询计划

　　在前面的例子中，请注意在查询计划中有这样一行：

MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE)

　　这个访问MAT_VIEW REWRITE的方法是新出现的。它表示已经访问了MV，而不是表或者段。这就可以让你在从名字上无法区分时知道是在使用表还是MV。

　　总结

　　在10g中，由于增加了新的Tuning Advisor，它能向你提供很多关于MV设计方面的建议而无需通过猜测方式进行。这使得管理MV容易多了。我特别细化能将建议生成一个完整的脚本使实施更加迅速，能节省很多时间这一特性。强制重写或取消查询这一特性在决策支持系统中非常有用。因为这样的系统不允许一个没有被重写的查询在数据库内疯狂执行。