无需改变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_mvenable query rewriteasselect t.month, p.product_id, sum(ps.purchase_price) as sum_of_sales, count (ps.purchase_price) as total_salesfrom time t, product p, purchases ps where t.time_key = ps.time_key and ps.product_id = p.product_idgroup 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_salesfrom time t, product p, purchases pswhere t.time_key = ps.time_key and ps.product_id = p.product_idgroup 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_salesfrom time t, product p, purchases pswhere t.time_key = ps.time_key and ps.product_id = p.product_idgroup 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_dimlevel time_key is time.time_keylevel month is time.monthlevel quarter is time.quarterlevel year is time.yearhierarchy 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_salesfrom time t, product p, purchases pswhere t.time_key = ps.time_key and ps.product_id = p.product_idgroup 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_salesfrom time t, product p, purchases pswhere 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_mvenable query rewriteasselect t.month, p.product_id, sum(ps.purchase_price) as sum_of_sales, count (ps.purchase_price) as total_salesfrom time t, product p, purchases pswhere t.time_key = ps.time_key and ps.product_id = p.product_id and t.year between 1997 and 2002group by t.month, p.product_id;
此物化视图可用于响应要求从1997年至2002年数据的查询，例如，清单 6中的查询要求2000年的销售额。
代码清单 6：只查询物化视图
select t.month, p.product_id, sum(ps.purchase_price) as sum_of_salesfrom time t, product p, purchases pswhere t.time_key = ps.time_key and ps.product_id = p.product_id and t.year = 2000group 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_salesfrom time t, product p, purchases pswhere t.time_key = ps.time_key and ps.product_id = p.product_id and t.year between 2000 and 2003group 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函数的物化视图
create materialized view monthly_sales_pct_mvenable query rewriteasselect 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_salesfrom time t, product p, purchases pswhere t.time_key = ps.time_key and ps.product_id = p.product_idgroup 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 pswhere 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 pswhere t.time_key = ps.time_key and ps.product_id = p.product_id and t.year between 1993 and 2003group 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_salesfrom time t, product p, purchases pswhere t.time_key = ps.time_key and ps.product_id = p.product_id and t.year between 1989 and 1999group by t.month, p.product_id; id operation name ---------------------------------------------------------1 select statement 1 sort group by 2 view 3 union-all 4 mat_view rewrite access full monthly_sales_1995_to_2000 5 mat_view rewrite access full monthly_sales_1990_to_1994 6 sort group by 7 nested loops 8 nested loops 9 table access full time 10 partition range iterator 11 table access full purchases 12 index range scan product_pk_index
清单 11中的查询需要从1989年至1999年的数据，所以查询改写可以使用物化视图monthly_sales_1990_to_1994和monthly_sales_1995_to_2000，并由细目表获得1989年的数据。这一过程实质上会比由细目表中获得所有数据更快一些。
oracle10g数据库在查询改写方面有其他几个改进。在这些改进中值得注意的是oracle10g数据库能够更好地支持集合运算符（union、 unionall等）增强了对多个表实例的查询的增强，并提供了对分区变化跟踪中的list 和range-list分区类型的支持。
工具
你可能会一边阅读本文，一边自言自语：“嗯，我想我理解了你的意思，但是否有某些工具可以为我完成所有这些工作呢？”答案是肯定的。事实上，这些工具还相当多。
oracle9i数据库引用了explain_mview和explain_rewrite。应用编程接口（api）explain_mview采用一个物化视图定义，并建议可使用何种类型的分区变化跟踪操作、是否可能进行快速刷新，以及可以完成何种类型的查询改写。当一个查询未被改写时，api explain_rewrite将告诉你sql查询为什么不使用查询改写。在两种情况下，工具包都会告诉你问题所在--例如，不能在一个特定列上进行连接，但两个包都不会准确告诉你如果解决这个问题。这时，就可以使用包含在oracle10g数据库中的两个新工具--tune_mview和sql access advisor来帮助你解决这个问题。
tune_mview api将告诉你如何编写物化视图，使其可以快速刷新，并可以使用本文所描述的尽可能多的高级查询改写类型。tune_mview api 的使用非常简单：只需要将你的物化视图语句交给它，它就会判定该物化视图的最佳形式。但是，如果你看到你的原始物化视图已经被转换为多个新版本，也不要感到奇怪。
让我们来看看tune_mview 如何能够转换你的物化视图。假设我们有一个简单的查询，并将其传递给explain_mview，如清单 12所示，判断该物化视图在当前形式下是否可以快速刷新。
代码清单 12
begin dbms_mview.explain_mview( 'create materialized view customer_mv build immediate refresh fast enable query rewrite as select c.customer_id, c.town, count(distinct(product_id)) as dist_promo_cnt from purchases ps, customer c where ps.customer_id = c.customer_id group by c.customer_id, c.town', 'id1'); end; /-- see if refresh fast capability is allowed (y) or not (n)select capability_name, possiblefrom mv_capabilities_tablewhere capability_name = 'refresh_fast' and statement_id = 'id1';capability_name p---------------------------------refresh_fast n
现在让我们使用相同的查询，并将其传递给tune_mview，如以下代码所示：
variable task_name varchar2(2000);begindbms_advisor.tune_mview (:task_name,'create materialized view customer_mv build immediate refresh fast enable query rewrite as select c.customer_id, c.town, count(distinct(product_id)) as dist_promo_cnt from purchases ps, customer c where ps.customer_id = c.customer_id group by c.customer_id, c.town');end;/
代码清单 13
select statement from user_tune_mview where task_name = :task_name;create materialized view easydw.customer_mv build immediaterefresh fast with rowid enable query rewriteas select easydw.purchases.product_id c1, easydw.customer.town c2, easydw.customer.customer_id c3, count(*) m1from easydw.purchases, easydw.customerwhere easydw.customer.customer_id = easydw.purchases.customer_idgroup by easydw.purchases.product_id, easydw.customer.town, easydw.customer.customer_id;目录视图user_tune_mview将显示所得到的物化视图，如清单 13所示。尽管它看起
来与我们的原始物化视图有点不同，但在可以使用原始物化视图的地方，仍然可以使用该
物化视图改写任何查询，此外，还可以快速刷新。
你也可以生成一个脚本来执行这些建议，你可能希望做的仅有修改就是改变物化视图的名称，以及指定物化视图应当放在哪里的存储语句和表空间。
现在，我们已经有了一个物化视图，但如果我们不知道创建什么物化视图，那么应当怎么办？这时，sql access advisor可以帮助你，因为它会浏览你的系统，并它认为需要的索引和物化视图。
这些建议是基于实际的工作负荷或根据你的模式所做出的假设提出的。当提供了sql语句的实际工作负荷时，将得到最好的结果。这一工作负荷可由sql缓存的当前内容、sql 调优集合（tuning set）、oracle9i summary advisor工作负荷或用户提供的工作负荷表（包含你已经定义的sql语句）获得。
sql access advisor既可以通过命令行api使用，也可以通过企业管理器（enterprise manager）的一部分--sql access advisor向导使用。使用该向导，在显示这些建议之前只需要完成三个步骤。让我们来看看如何通常命令行界面使用sql access advisor：
首先，创建一个包含，这一调优过程所有信息的任务。然后，该任务将利用工作负荷信息来生成作为任务的一部分存储的调优建议。因此，整个过程是完全独立的，而且允许各个任务稍有不同，以便人们能够看到对配置进行修改后的效果。在清单 14所示的示例中，是通过手工定义sql语句对工作负荷进行定义的。
代码清单 14
declaretask_desc varchar2(100);task_id number;task_name varchar2(30);workload_name varchar2(30);begin task_name := 'task_mag'; dbms_advisor.create_task (dbms_advisor.sqlaccess_advisor, task_id, task_name, 'my advisor task', dbms_advisor.sqlaccess_warehouse); dbms_advisor.set_task_parameter ('task_mag', 'evaluation_only', 'false'); dbms_advisor.set_task_parameter ('task_mag', 'execution_type', 'full'); -- create the workload workload_name :='workload_mv'; dbms_advisor.create_sqlwkld(workload_name, 'mv workload' , null); -- now link the two together dbms_advisor.add_sqlwkld_ref(task_name, workload_name) ; -- add a sql statement dbms_advisor.add_sqlwkld_statement (workload_name,'app','action', null,15,3000,423,507,60,704, 3,'16-feb-2002',80, 'easydw', 'select c.customer_id, c.town, count(distinct(product_id)) as dist_promo_cnt from purchases ps, customer c where ps.customer_id = c.customer_id group by c.customer_id, c.town');end;/
一旦定义了工作负荷和任务，就可以生成如下所示的建议，该建议使用了execute_task 并指定了所创建任务的名字--task_mag：
execute dbms_advisor.execute_task ('task_mag');
根据工作负荷的复杂性，生成建议的时间可以由几秒到几分钟不等。因此，尽管这个过程可以交互式运行，但你可能希望考虑提交一个任务，这就是企业管理器中的向导所要完成的工作。
你可以通过查询表user_advisor_recommendations 来快速检查是否有关于task_name的建议。在对本例进行此操作时，我们会看到已经提出了一个建议。
select 'no of recommendations:' , count(*) from user_advisor_recommendations r where task_name='task_mag';'noofrecommendations:' count(*)---------------------- ----------no of recommendations: 1
单个建议可以导致多个操作。对于此示例，sql access advisor建议创建物化视图日志、一个create materialized view，以及一个用来分析物化视图的调用（受版面限制，这里未给出）。
尽管你可以查询各种目录视图来查看这些操作，但查看它们的最简单方法就是生成一个脚本，如下所示：
executedbms_advisor.create_file(dbms_advisor.get_task_script('task_mag'), 'advisor_results', 'mag_example.sql');
在清单 15中，你可以看到该脚本的一段摘录，显示了为我们的查询所建立的物化视图。
代码清单 15
rem access advisor remrem username: easydwrem task: my_taskrem execution date: 20/05/2003 14:36rem...create materialized view "easydw"."mv$$_002d0000"refresh fast with rowidenable query rewriteas select easydw.purchases.product_id c1, easydw.customer.town c2, easydw.customer.customer_id c3, count(*) m1 from easydw.purchases, easydw.customer where easydw.customer.customer_id = easydw.purchases.customer_id group by easydw.purchases.product_id, easydw.customer.town, easydw.customer.customer_id;...
结论
通过使用查询改写，你可以利用几个物化视图显著改进许多查询的性能，从而减少了保持物化视图与基础细目数据同步所需要的磁盘空间占用与刷新时间。
