使用查询改写提高查询性能

无需改变SQL查询就可以大幅提高查询性能。
你是否为等待你的查询返回结果而感到疲惫？你是否已经为增强索引和调优SQL而感到疲惫，但仍然不能提高查询性能？那么，你是否已经考虑创建物化视图？有了物化视图，那些过去需要数小时运行的报告可以在几分钟内完成。物化视图可以包括联接（join）和集合（aggregate），它提供了一种储存预计算结果的方法。
在执行一个查询时，优化器会判定访问物化视图或数据驻留的基础表是否更快一些。如果优化器判定查询物化视图是更好的解决方案，那么优化器会在一个被称为“查询改写”（query rewrite）的过程中改写SQL查询。在这个过程中，不需要对任何SQL或应用程序代码进行修改，所以任何利用SQL访问数据库的应用程序或特定查询工具都可得益于使用物化视图。当为计算结果而需要访问的数据数量远大于结果（如集合）的大小时，最适合使用查询改写，但是它也可被用于加速昂贵的联接或规划。
本文首先介绍了优化器可以执行的查询改写类型。然后，它讨论了帮助确定创建最佳物化视图集的工具，使优化器能够改写多个查询。利用这些工具创建的物化视图在其基础数据发生变化时还可以快速刷新。如果你不知道创建一个物化视图、一个索引或同时创建两者哪种更好，那么在Oracle数据库10g中引入的SQL Access Advisor可以通过分析给定的工作负荷帮助你做出决定。
查询改写类型
可能有许多类型的查询改写；当物化视图的定义查询与查询的文本完全匹配时，就发生最简单和最显著类型的查询改写。但是，当相同物化视图可用于相应多个查询时，就可以实现查询改写的最大好处。现在，我们将举例说明一些Oracle优化器使用的规则，以确定它是否将使用物化视图来响应。
对于本文中的示例，可以考虑将一个星形模式中的PURCHASES表看作事实表（fact table），其范围由time_key划分。维度表（dimension table）--TIME、PRODUCT和CUSTOMERS--包含主键 time_key、product_id和cust_id。在PURCHASES表中有引用各个维度表的外键约束。
考虑一下清单 1中所创建的物化视图，该视图按月按product_id计算销售总额和销售总次数。注意：对于用于查询改写的物化视图，必须有ENABLE QUERY REWRITE子句。还有，初始化参数QUERY_REWRITE_ENABLED必须被设置为TRUE。
代码清单 1：创建月销售物化视图

CREATE MATERIALIZED VIEW monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales,
COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.month, p.product_id;
集合计算
在本文的示例中，我们将说明物化视图的查询并显示由EXPLAIN PLAN得到的执行计划。清单 2中的查询要求按月和按产品的平均采购价格。优化器可以使用物化视图monthly_sales_mv，利用SUM和COUNT集合计算平均采购价格。这个示例说明了一种叫做“集合计算”的技术。
代码清单 2：获得平均(AVG)采购价格

SELECT t.month, p.product_id, AVG(ps.purchase_price) as avg_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.month, p.product_id;

Id Operation Name

SELECT STATEMENT
MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV

Joinback
joinback技术非常有用，因为它允许当物化视图中没有列时进行查询改写。清单 3中的查询要求按月和按产品类别的销售总额，而该物化视图中并没有product.category列。然而，产品表的主键product_id列则位于物化视图中。因此，优化器可以将物化视图与产品表联接起来以得到产品类别。
代码清单 3：通过joinback获得销售总额

SELECT t.month, p.category, SUM(ps.purchase_price) as sum_of_sales

FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.month, p.category;

Id Operation Name

0 SELECT STATEMENT
1 SORT GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCT
4 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV

使用维度进行查询改写
在一个使用维度建模技巧设计的典型数据仓库中，数据中存在着著名的“层次关系”。例如，在时间层次中，“天”积累成“月”，“月”又积累成“年”。在Oracle数据库中，可以使用CREATE DIMENSION语句创建一个叫做“DIEMNSION”的对象，向优化器声明这种关系。维度对象是一个描述性对象，除了其元数据外，它不占用空间。使用DIMENSION对象声明的关系据说是可信的。Oracle不会验证这一关系对于你的数据是否一定成立，它只是假设数据库管理员已经判定这些关系是正确的。可信信息的其他示例是使用NOVALIDATE RELY标记的约束及注册为物化视图的先存表。
对于采用可信信息（包括维度）的查询改写，初始化参数QUERY_ REWRITE_INTEGRITY必须被设置为TRUSTED，如下所示：

ALTER SESSION SET query_rewrite_integrity = TRUSTED;

例如，假设有一个时间维度，其声明如下：

CREATE DIMENSION time_dim
LEVEL time_key IS time.time_key
LEVEL month IS time.month
LEVEL quarter IS time.quarter
LEVEL year IS time.year
HIERARCHY calendar_rollup (
time_key CHILD OF
month CHILD OF
quarter CHILD OF
year
)
ATTRIBUTE time_key determines (day_of_week, holiday)
ATTRIBUTE month determines (month_name);

现在，如果具有清单 4中要求按年的销售额的查询，你仍然可以使用monthly_sales_mv物化视图，因为维度对象中的HIERARCHY子句告诉Oracle数据库月销售额可以积累成年销售额。它利用前面描述的joinback技巧由物化视图中的“月”列得到“年”列的值。
代码清单 4：通过joinback和HIERARCHY获得销售总额

SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.year, p.category;

Id Operation Name

0 SELECT STATEMENT
1 SORT GROUP BY
2 HASH JOIN
3 HASH JOIN
4 VIEW
5 SORT UNIQUE
6 TABLE ACCESS FULL TIME
7 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV
8 TABLE ACCESS FULL PRODUCT
维度的ATTRIBUTE子句指明了一对一关系。例如，你可以判定从time_key开始是一周中的哪一天。假设你希望得到每年1月份的销售总额：你仍然可以使用清单 5中所示的monthly_sales_mv物化视图。注意该查询的WHERE子句如何具有一个在物化视图中没有出现的选择条件。
代码清单 5：通过joinback和ATTRIBUTE获得销售总额

SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.month_name = ’January’
GROUP BY t.year, p.category;

Id Operation Name

0 SELECT STATEMENT
1 SORT GROUP BY
2 HASH JOIN
3 HASH JOIN
4 VIEW
5 SORT UNIQUE
6 TABLE ACCESS FULL TIME
7 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV
8 TABLE ACCESS FULL PRODUCT

如果优化器并未如期改写一个查询，可以使用DBMS_MVIEW .EXPLAIN_REWRITE 过程来诊断该问题。这一特性出现在Oracle9i数据库及以后的版本中。
过滤后的数据
到目前为止，我们所给出的所有示例都使用了与采购表中的所有数据对应的物化视图。Oracle9i数据库具备在物化视图仅有一个数据子集情况下改写查询的能力。例如，如果你只对1997年到2002年的销售额感兴趣，你可以将物化视图修改如下：

CREATE MATERIALIZED VIEW five_yr_monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, p.product_id,
SUM(ps.purchase_price) as sum_of_sales,
COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.year between 1997 and 2002
GROUP BY t.month, p.product_id;

此物化视图可用于响应要求从1997年至2002年数据的查询，例如，清单 6中的查询要求2000年的销售额。
代码清单 6：只查询物化视图
SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.year = 2000
GROUP BY t.month, p.product_id;

Id Operation Name

SELECT STATEMENT
1 HASH JOIN
2 VIEW
3 SORT UNIQUE
4 TABLE ACCESS FULL TIME
5 MAT_VIEW REWRITE ACCESS FULL FIVE_YR_MONTHLY_SALES_MV

在Oracle9i数据库中，如果物化视图中没有查询所需要的全部数据，查询就不会使用物化视图。在Oracle数据库10g中，已经放松了这一限制，因此查询改写可以由物化视图中获得尽可能多的数据，并利用细目表获得物化视图中没有的数据。和往常一样，优化器在做出执行此操作的决定时考虑了有改写和无改写情况下的查询成本。
例如，清单 7中的查询要求2000年至2003年之间的月销售额，它将使用从2000年至2002年的物化视图，而只需要2003年的细目表。
代码清单 7：查询物化视图和细目表

SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.year BETWEEN 2000 and 2003
GROUP BY t.month, p.product_id;
Id Operation Name

0 SELECT STATEMENT
1 SORT GROUP BY
2 VIEW
3 UNION-ALL
4 HASH JOIN
5 VIEW
6 SORT UNIQUE
7 TABLE ACCESS FULL TIME
8 MAT_VIEW REWRITE ACCESS FULL FIVE_YR_MONTHLY_SALES_MV
9 SORT GROUP BY
10 NESTED LOOPS
11 HASH JOIN
12 TABLE ACCESS FULL TIME
13 PARTITION RANGE ALL
14 TABLE ACCESS FULL PURCHASES
15 INDEX RANGE SCAN PRODUCT_PK_INDEX

使用失效的物化视图进行查询改写
你可能想知道如果细目表中的数据发生了变化会发生什么情况。查询改写仍将使用物化视图吗？答案决定于初始化参数QUERY_REWRITE_ INTEGRITY的设置。QUERY_REWRITE_INTEGRITY参数有三个取值：
STALE_TOLERATED表示即使细目表中的数据已经发生了变化，也仍然使用物化视图。
TRUSTED 表示物化视图未失效时才使用该视图。但是，查询改写可以使用信任关系，如那些由维度对象或尚未生效的约束所声明的关系。
ENFORCED（缺省）表示当物化视图保证能给出与使用细目表相同的结果时才使用它。使用这一参数意味着查询改写将不使用失效的物化视图或信任关系。
正确的设置决定于应用程序的数据需求。使用失效物化视图的查询改写可能会产生与没有使用查询改写时不同的结果。然而，如果使用细目数据，可能会因为响应查询需要处理的大量数据而使性能恶化。在一个数据仓库中，通常使用TRUSTED完整级别，因为这样才可以保证你只使用那些具有最新数据的物化视图；然而，被声明为正确（可信任）的关系也可用于查询改写。在大多数数据仓库中，这些关系已经在提取、转换和加载（ETL）过程得到了验证，因此不再需要进行验证。
分区变化跟踪
在Oracle9i数据库中，Oracle引入了分区变化跟踪（PCT，Partition Change Tracking）。利用这一特性，Oracle9i数据库可以跟踪物化视图的哪一部分对应于分区细目表的已更新部分。因此，如果查询不需要已更新表的部分，那么该物化视图仍然可以使用。
为了在物化视图中跟踪一个细目表的变化，必须对该表进行分区，并且该物化视图（在SELECT列表中）必须包括细目表的分区键或一个特殊函数：DBMS_MVIEW.PMARKER。此函数为细目表中的每个分区生成一个唯一的标识符。
例如，由time_key对采购表进行分区。清单 8中创建的物化视图与前面使用的monthly_sales_mv 物化视图几乎完全相同，只是该物化视图在采购表上包含了一个附加的DBMS_MVIEW.PMARKER函数。通过包含这一函数，当更新采购表时该物化视图允许PCT。注意：该物化视图自身并不需要被分区。
代码清单 8：具有DBMS_MVIEW.PMARKER函数的物化视图

使用查询改写提高查询性能

［作者：chensheng913 转贴自：csdn 点击数：1 更新时间：2005-11-9 ］
减小字体增大字体
无需改变SQL查询就可以大幅提高查询性能。
你是否为等待你的查询返回结果而感到疲惫？你是否已经为增强索引和调优SQL而感到疲惫，但仍然不能提高查询性能？那么，你是否已经考虑创建物化视图？有了物化视图，那些过去需要数小时运行的报告可以在几分钟内完成。物化视图可以包括联接（join）和集合（aggregate），它提供了一种储存预计算结果的方法。
在执行一个查询时，优化器会判定访问物化视图或数据驻留的基础表是否更快一些。如果优化器判定查询物化视图是更好的解决方案，那么优化器会在一个被称为“查询改写”（query rewrite）的过程中改写SQL查询。在这个过程中，不需要对任何SQL或应用程序代码进行修改，所以任何利用SQL访问数据库的应用程序或特定查询工具都可得益于使用物化视图。当为计算结果而需要访问的数据数量远大于结果（如集合）的大小时，最适合使用查询改写，但是它也可被用于加速昂贵的联接或规划。
本文首先介绍了优化器可以执行的查询改写类型。然后，它讨论了帮助确定创建最佳物化视图集的工具，使优化器能够改写多个查询。利用这些工具创建的物化视图在其基础数据发生变化时还可以快速刷新。如果你不知道创建一个物化视图、一个索引或同时创建两者哪种更好，那么在Oracle数据库10g中引入的SQL Access Advisor可以通过分析给定的工作负荷帮助你做出决定。
查询改写类型
可能有许多类型的查询改写；当物化视图的定义查询与查询的文本完全匹配时，就发生最简单和最显著类型的查询改写。但是，当相同物化视图可用于相应多个查询时，就可以实现查询改写的最大好处。现在，我们将举例说明一些Oracle优化器使用的规则，以确定它是否将使用物化视图来响应。
对于本文中的示例，可以考虑将一个星形模式中的PURCHASES表看作事实表（fact table），其范围由time_key划分。维度表（dimension table）--TIME、PRODUCT和CUSTOMERS--包含主键 time_key、product_id和cust_id。在PURCHASES表中有引用各个维度表的外键约束。
考虑一下清单 1中所创建的物化视图，该视图按月按product_id计算销售总额和销售总次数。注意：对于用于查询改写的物化视图，必须有ENABLE QUERY REWRITE子句。还有，初始化参数QUERY_REWRITE_ENABLED必须被设置为TRUE。
代码清单 1：创建月销售物化视图

SELECT t.month, p.product_id, AVG(ps.purchase_price) as avg_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.month, p.product_id;

Id Operation Name

SELECT STATEMENT
MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV

SELECT t.month, p.category, SUM(ps.purchase_price) as sum_of_sales

FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.month, p.category;

Id Operation Name

0 SELECT STATEMENT
1 SORT GROUP BY
2 HASH JOIN
3 TABLE ACCESS FULL PRODUCT
4 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV

ALTER SESSION SET query_rewrite_integrity = TRUSTED;

例如，假设有一个时间维度，其声明如下：

SELECT t.year, p.category, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY t.year, p.category;

Id Operation Name

0 SELECT STATEMENT
1 SORT GROUP BY
2 HASH JOIN
3 HASH JOIN
4 VIEW
5 SORT UNIQUE
6 TABLE ACCESS FULL TIME
7 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_MV
8 TABLE ACCESS FULL PRODUCT

Id Operation Name

SELECT STATEMENT
1 HASH JOIN
2 VIEW
3 SORT UNIQUE
4 TABLE ACCESS FULL TIME
5 MAT_VIEW REWRITE ACCESS FULL FIVE_YR_MONTHLY_SALES_MV

CREATE MATERIALIZED VIEW monthly_sales_pct_mv
ENABLE QUERY REWRITE
AS
SELECT DBMS_MVIEW.PMARKER(ps.rowid) pm, t.month, p.product_id,
SUM(ps.purchase_price) as sum_of_sales,
COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id
GROUP BY DBMS_MVIEW.PMARKER(ps.rowid), t.month, p.product_id;

现在，假设我们向采购表中增加一个2003年4月的新分区，而且一个用户发出了一个请求2002年3月的数据的查询，如清单 9所示。在此查询中，我们并不关心2003年4月已更新的数据，所以将利用物化图对其进行改写，即使该物化视图已经失效也是如此。
代码清单 9：使用失效的物化视图进行查询改写

SELECT t.month, p.product_id, SUM(ps.purchase_price)
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
ps.time_key >= TO_DATE(’01-03-2003’, ’DD-MM-YYYY’) AND
ps.time_key < TO_DATE(’01-04-2003’, ’DD-MM-YYYY’)
GROUP BY t.month, p.product_id;

Id Operation Name

0 SELECT STATEMENT
1 SORT GROUP BY
2 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_PCT_MV
如果查询要求从1月至4月的数据，在Orcale9i中，将不会为使用物化视图而对该查询进行改写。但在Oracle数据库10g中，可以使用MONTHLY_SALES_ PCT_MV和细目表的结合对该查询进行改写。
使用多个物化视图进行查询改写
前面曾经提到，在Oracle10g数据库中，查询改写已经得到了增强，所以它可以使用一个物化视图的部分数据以及细目表的其余数据来响应查询。事实上，查询改写可以结合使用两个或多个物化视图。例如，假设你为每5年的数据价值维护一个独立的物化视图： monthly_sales_1990-1994、 monthly_sales_1995_to_2000、 monthly_sales_2001_to_2005，等等。
那么，对于需要从1993年至2003年数据的清单 10中的查询，查询改写可以利用全部的三个物化视图。
代码清单 10

SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales,
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.year between 1993 and 2003
GROUP BY t.month, p.product_id;

Id Operation Name
---------------------------------------------------------
0 SELECT STATEMENT
1 SORT GROUP BY
2 VIEW
3 UNION-ALL
4 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_2001_TO_2005
5 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_1995_TO_2000
6 MAT_VIEW REWRITE ACCESS FULL MONTHLY_SALES_1990_TO_1994
代码清单 11 SELECT t.month, p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
ps.product_id = p.product_id AND
t.year between 1989 and 1999
GROUP BY t.month, p.product_id;

Id Operation &