`
isiqi
  • 浏览: 16557948 次
  • 性别: Icon_minigender_1
  • 来自: 济南
社区版块
存档分类
最新评论

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

阅读更多

无需改变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函数的物化视图

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                        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, possible
FROM mv_capabilities_table
WHERE capability_name = 'REFRESH_FAST' and STATEMENT_ID = 'ID1';



CAPABILITY_NAME                P
---------------------------------
REFRESH_FAST                   N

现在让我们使用相同的查询,并将其传递给TUNE_MVIEW,如以下代码所示:

variable task_name varchar2(2000);

BEGIN
DBMS_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 IMMEDIATE

REFRESH FAST WITH ROWID 
ENABLE QUERY REWRITE

AS 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;


目录视图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

DECLARE


task_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,以及一个用来分析物化视图的调用(受版面限制,这里未给出)。

尽管你可以查询各种目录视图来查看这些操作,但查看它们的最简单方法就是生成一个脚本,如下所示:

execute
dbms_advisor.create_file(dbms_advisor.get_task_script('Task_mag'),
 'ADVISOR_RESULTS', 'mag_example.sql');

清单 15中,你可以看到该脚本的一段摘录,显示了为我们的查询所建立的物化视图。

代码清单 15

Rem  Access Advisor 
Rem
Rem  Username:        EASYDW

Rem  Task:            My_Task
Rem  Execution date:  20/05/2003 14:36

Rem
...

CREATE MATERIALIZED VIEW "EASYDW"."MV$$_002D0000"
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS 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;
...

结论

通过使用查询改写,你可以利用几个物化视图显著改进许多查询的性能,从而减少了保持物化视图与基础细目数据同步所需要的磁盘空间占用与刷新时间。

分享到:
评论

相关推荐

    Oracle查询优化改写技巧与案例2.zip

    《Oracle查询优化改写技巧与案例》不讲具体语法,只是以案例的形式介绍各种查询语句的..., 如果您是开发人员,经常与Oracle打交道,那么《Oracle查询优化改写技巧与案例》可以帮助您处理复杂的需求,写出高性能的语句。

    Oracle查询优化改写技巧与案例

    , 如果您是开发人员,经常与Oracle打交道,那么《Oracle查询优化改写技巧与案例》可以帮助您处理复杂的需求,写出高性能的语句。如果您是运维人员,则《Oracle查询优化改写技巧与案例》可以帮助您更快地完成慢语句的...

    Oracle查询优化改写 技巧与案例.pdf

    重写这些查询语句,使用 EXISTS 或 JOIN 来替代,通常能够提高查询的效率。 案例分析是理解这些技巧的最佳途径。在本文件中,作者会通过具体的案例来演示每一种优化技巧的应用。通过这些实际案例,读者不仅可以学习...

    【oracle】oracle查询优化改写

    查询优化是性能优化的核心部分,它涉及到如何以最有效的方式执行SQL查询,从而提高数据检索的速度并减少系统资源的消耗。在Oracle中,查询优化主要依赖于查询改写和优化器的选择。 查询改写是Oracle数据库优化策略...

    oracle 查询优化改写

    "Oracle查询优化改写"是一个专业领域,旨在通过调整SQL语句的结构和逻辑,提高查询速度,降低资源消耗,从而提升整体系统性能。本文将深入探讨这个主题,结合大量实际案例,提供一系列实用的技巧和方法。 首先,...

    《Oracle查询优化改写技巧与案例》PDF版本下载.txt

    查询优化是指在数据库管理系统(DBMS)中选择执行SQL语句的最佳方式,以最小化资源消耗并提高查询性能的过程。它涉及到多个方面,如索引使用、连接策略、表扫描方法等。 #### 1.2 为什么需要查询优化 - **提升系统...

    Oracle查询优化改写-技巧与案例

    4. **子查询和临时表**:子查询可能会导致性能问题,书中的案例可能会演示如何通过重写查询、使用WITH子句(公共表表达式)或者临时表来优化子查询。 5. **分页查询**:在大数据量场景下,高效的分页查询必不可少。...

    Oracle查询优化改写 技巧与案例 .pdf

    通过使用子查询替换、连接优化、并行查询、物化视图等技术,可以改善查询性能。例如,将嵌套循环转换为哈希连接或排序归并连接,或者使用并行查询来分摊计算负载,都能提高查询速度。此外,避免全表扫描,尽可能利用...

    Oracle查询优化改写 技巧与案例_高清带书签版本

    如果您是开发人员,经常与Oracle打交道,那么《Oracle查询优化改写技巧与案例》可以帮助您处理复杂的需求,写出高性能的语句。如果您是运维人员,则《Oracle查询优化改写技巧与案例》可以帮助您更快地完成慢语句的...

    SQL Server查询性能分析与查询效率提高.pdf

    此外,合理使用查询提示(Query Hint)也是提高SQL Server查询效率的常用技术手段。例如,使用FORCE ORDER提示可以强制优化器按照指定的表连接顺序生成执行计划。 总之,SQL Server查询性能的分析和优化是一个涉及...

    oracle查询优化改写技巧与案例

    , 如果您是开发人员,经常与Oracle打交道,那么《Oracle查询优化改写技巧与案例》可以帮助您处理复杂的需求,写出高性能的语句。如果您是运维人员,则《Oracle查询优化改写技巧与案例》可以帮助您更快地完成慢语句的...

    Oracle查询优化改写技巧与案例.rar

    - 子查询可能导致多次表扫描,考虑是否可以转换为连接查询或者使用表变量、临时表来提高性能。 5. **使用绑定变量** - 绑定变量能避免硬解析,提高SQL语句的复用率。尽量避免在SQL语句中使用常量,改为使用绑定...

    Oracle查询优化改写 技巧与案例

    总结,Oracle查询优化改写是数据库管理中的核心技能,通过深入理解SQL语句、索引、表设计和参数设置,我们可以有效地提高查询性能,提升整体系统效率。在实际工作中,需要结合具体业务场景,灵活运用这些技巧,以...

    oracle查询优化改写 技巧与案例

    Oracle查询优化是提高数据库性能的重要手段之一。通过对执行计划、统计信息和索引等方面的深入理解和运用,可以有效提升查询效率,降低系统资源消耗。同时,结合具体案例进行实践分析,能够更好地掌握查询优化的方法...

    通过改写缓冲文件提高VocieXML性能的研究

    这些内容详细阐述了VoiceXML系统的运行机制,以及如何通过优化语法处理和文档管理来提高性能。 本文的研究为VoiceXML系统的性能优化提供了理论和实践的指导,特别是对于希望提高语音服务质量和效率的业务提供商和...

Global site tag (gtag.js) - Google Analytics