使用多个 MV 进行查询重写
Oracle8i 中引入的查询重写特性在数据仓库开发人员和 DBA 中轰动一时。从本质上而言，它将用户查询重写为从 MV 而非表中进行选择以利用现成的摘要。例如，请考虑以下一家大型连锁酒店的数据库中的三个表。
SQL> DESC HOTELS Name Null?Type ----------------------------------------- -------- ------------- HOTEL_ID NOT NULL NUMBER(10) CITY VARCHAR2(20) STATE CHAR(2) MANAGER_NAME VARCHAR2(20) RATE_CLASS CHAR(2) SQL> DESC RESERVATIONS Name Null?Type ----------------------------------------- -------- ------------- RESV_ID NOT NULL NUMBER(10) HOTEL_ID NUMBER(10) CUST_NAME VARCHAR2(20) START_DATE DATE END_DATE DATE RATE NUMBER(10) SQL> DESC TRANS Name Null?Type ----------------------------------------- -------- ------------- TRANS_ID NOT NULL NUMBER(10) RESV_ID NOT NULL NUMBER(10) TRANS_DATE DATE ACTUAL_RATE NUMBER(10)
表 HOTELS 保存酒店的相关信息。当顾客预订酒店时，将在表 RESERVATIONS（包含房间价格报价）中创建一个记录。当顾客在酒店结帐时，将在另一个表 TRANS 中记录现金交易。
但在结帐前，酒店可能决定根据订房情况、升级、优惠等因素向顾客提供不同的房价。因此，最终的房价可能与预订时的报价不同，而且可以每天都各不相同。为正确记录这些价格变化，表 TRANS 有一行专门用来保存每天的房价信息。
为缩短查询响应时间，您可能决定根据用户发出的不同查询构建 MV，如：
create materialized view mv_hotel_resv refresh complete enable query rewrite as select city, resv_id, cust_name from hotels h, reservations r where r.hotel_id = h.hotel_id;
和
create materialized view mv_actual_sales refresh complete enable query rewrite as select resv_id, sum(actual_rate) from trans group by resv_id;
因此，如果设置了某些参数（如 query_rewrite_enabled = true），则类似如下所示的查询
select city, cust_name from hotels h, reservations r where r.hotel_id = h.hotel_id;
将重写为
select city, cust_name from mv_hotel_resv;
您可以通过运行该查询并启用自动跟踪来确认 MV。
SQL> set autot traceonly explain SQL> select city, cust_name 2> from hotels h, reservations r 3> where r.hotel_id = h.hotel_id; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=80 Bytes=2480) 1 0 MAT_VIEW ACCESS (FULL) OF 'MV_HOTEL_RESV' (MAT_VIEW) (Cost=3 Card=80 Bytes=2480)
注意，查询是如何从物化视图 MV_HOTEL_RESV 而非表 HOTELS 和 RESERVATIONS 中进行选择的。这正是您所需要的。同样，当您编写一个查询来汇总每个预订编号的实际价格时，将使用物化视图 MV_ACTUAL_SALES 而非表 TRANS。
我们来采用一个不同的查询。如果要查明每个城市的实际销售额，则将发出
select city, sum(actual_rate) from hotels h, reservations r, trans t where t.resv_id = r.resv_id and r.hotel_id = h.hotel_id group by city;
注意此查询结构：从 MV_ACTUAL_SALES 中，您可以获得 RESV_ID 和预订的总销售额。从 MV_HOTEL_RESV 中，您可以获得 CITY 和 RESV_ID。
您能将这两个 MV 连接在一起吗？当然可以，但在 Oracle 数据库 10g 第 2 版之前，查询重写机制只使用两个 MV 中的一个（而非两个）自动重写用户查询。
以下是 Oracle9i 数据库中的执行计划输出。您可以看到，只使用了 MV_HOTEL_RESV 和 TRANS 的整表扫描。
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=6 Bytes=120) 1 0 SORT (GROUP BY) (Cost=8 Card=6 Bytes=120) 2 1 HASH JOIN (Cost=7 Card=516 Bytes=10320) 3 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_HOTEL_RESV' (MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=1040) 4 2 TABLE ACCESS (FULL) OF 'TRANS' (TABLE) (Cost=3 Card=516 Bytes=3612)
即使 MV 可用，该方法也将生成一个非最优的执行计划。唯一的救济就是创建另一个将所有三个表连接在一起的 MV。但该方法将导致 MV 的增多，从而大大增加刷新 MV 所需的时间。
Oracle 数据库 10g 第 2 版解决了此问题。现在，以上查询将重写为使用两个 MV，如执行计划中所示。
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=6 Bytes=120) 1 0 SORT (GROUP BY) (Cost=8 Card=6 Bytes=120) 2 1 HASH JOIN (Cost=7 Card=80 Bytes=1600) 3 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=560) 4 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_HOTEL_RESV' (MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=1040)
注意，该执行计划是如何只使用了 MV 而未使用任何其他基表的。
该增强功能在数据仓库中具有显著的优点，这是因为您不必为每个可能的查询创建和刷新 MV。相反，你可以在关键地方创建几个没有太多连接和聚合的 MV，Oracle 将使用它们来重写查询。