高速高效 创建物化视图提高查询速度

    来源：赛迪网技术社区    作者：sanandi

你是否为等待你的查询返回结果而感到疲惫？你是否已经为增强索引和调优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数据库及以后的版本中.