Oracle 10g R2特性之数据仓库和集成特性

使用多个 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 将使用它们来重写查询。