ITPub博客

首页 > 数据库 > Oracle > 一个复合索引的优化案例

一个复合索引的优化案例

原创 Oracle 作者:hooca 时间:2016-04-15 14:05:03 0 删除 编辑
示例SQL语句:

点击(此处)折叠或打开

  1. select distinct A.time_id, A.seller, a.quantity_sold, A.amount_sold, B.tax_country
  2. from sales_A A
      join sales_B B on
        B.prod_id = A.prod_id and
        B.cust_id = A.cust_id and
        B.time_id = A.time_id and
        B.channel_id = A.channel_id
      left join (
        select promo_id, channel_id, TAX_COUNTRY
          from sales_AD9598
          where (prod_id = 110) and (cust_id = 1541239) and (time_id = to_date('2012-04-15','YYYY-MM-DD'))
        union all
        select promo_id, channel_id, TAX_COUNTRY
          from sales_ID9902
          where (prod_id = 110) and (cust_id = 1541239) and (time_id = to_date('2012-04-15','YYYY-MM-DD'))
        union all
        select promo_id, channel_id, TAX_COUNTRY
          from sales_AK0306
          where (prod_id = 110) and (cust_id = 1541239) and (time_id = to_date('2012-04-15','YYYY-MM-DD'))
        union all
        select promo_id, channel_id, TAX_COUNTRY
          from sales_IK0711
          where (prod_id = 110) and (cust_id = 1541239) and (time_id = to_date('2012-04-15','YYYY-MM-DD'))
        union all
        select AS9704.promo_id, AS9704.channel_id, AS9704.TAX_COUNTRY
          from sales_AS9704 AS9704
            left join sales_AD9598 AD9598 on
              AS9704.prod_id = AD9598.prod_id and AS9704.cust_id = AD9598.cust_id and
              AS9704.time_id = AD9598.time_id and AS9704.channel_id = AD9598.channel_id and
              AS9704.promo_id = AD9598.promo_id
            left join sales_AK0306 AK0306 on
              AS9704.prod_id = AK0306.prod_id and AS9704.cust_id = AK0306.cust_id and
              AS9704.time_id = AK0306.time_id and AS9704.channel_id = AK0306.channel_id and
              AS9704.promo_id = AK0306.promo_id
          where (AS9704.prod_id = 110) and (AS9704.cust_id = 1541239) and
            (AS9704.time_id = to_date('2012-04-15','YYYY-MM-DD'))
        union all
        select IS0108.promo_id, IS0108.channel_id, IS0108.TAX_COUNTRY
          from sales_IS0108 IS0108
            left join sales_ID9902 ID9902 on
              IS0108.prod_id = ID9902.prod_id and IS0108.cust_id = ID9902.cust_id and
              IS0108.time_id = ID9902.time_id and IS0108.channel_id = ID9902.channel_id and
              IS0108.promo_id = ID9902.promo_id
            left join sales_IK0711 IK0711 on
              IS0108.prod_id = IK0711.prod_id and IS0108.cust_id = IK0711.cust_id and
              IS0108.time_id = IK0711.time_id and IS0108.channel_id = IK0711.channel_id and
              IS0108.FULFILLMENT_CENTER = IK0711.FULFILLMENT_CENTER
          where (IS0108.prod_id = 110) and (IS0108.cust_id = 1541239) and
            (IS0108.time_id = to_date('2012-04-15','YYYY-MM-DD'))
      ) D on B.promo_id = D.promo_id and B.channel_id = D.channel_id
    WHERE (B.prod_id = 110) and (B.cust_id = 1541239) and
    (B.time_id = to_date('2012-04-15','YYYY-MM-DD')) and (B.channel_id = 2)
    order by A.quantity_sold, A.amount_sold;

其执行计划如下
 OPERATION   OBJECT_NAME   OPTIONS   COST 
 SELECT STATEMENT 
         120191 
    
 SORT 
     ORDER BY   120191 
         
 HASH 
     UNIQUE   120190 
              
 HASH JOIN 
     OUTER   120189 
                   
 Access Predicates 
                   
 HASH JOIN 
         60147 
                   
 VIEW 
         60041 
                        
 UNION-ALL 
           
                             
 TABLE ACCESS 
 SALES_AD9598   FULL   1776 
                             
 TABLE ACCESS 
 SALES_ID9902   FULL   3197 
                             
 TABLE ACCESS 
 SALES_AK0306   FULL   4293 
                             
 TABLE ACCESS 
 SALES_IK0711   FULL   12613 
                             
 HASH JOIN 
     OUTER   12592 
                             
 HASH JOIN 
     OUTER   25570 

都是通过全表扫描访问的。
注意到A和B的连接字段是prod_id, cust_id, time_id, channel_id;
主查询的筛选条件是表B的以上字段;
表AD,ID,AK,IK的筛选条件prod_id,cust_id,time_id,包括其中;
AS连(AD+AK)的连接字段包括了prod_id, cust_id, time_id, channel_id;
IS连(ID+IK)的连接字段包括了prod_id, cust_id, time_id, channel_id;

最后决定:在表A,B,AD,ID,AK,IK,AS,IS上都建立(prod_id,cust_id,time_id,channel_id)的复合索引,例:

点击(此处)折叠或打开

  1. create index sales_A_pk on sales_A(
  2. prod_id, cust_id, time_id, channel_id);
再次查看执行计划,限于篇幅,只截取部分



优化器选择了新创建的索引,执行效率飞速提升。

进一步讨论:如果主查询中的筛选条件
WHERE (B.prod_id = 110) and (B.cust_id = 1541239) and
(B.time_id = to_date('2012-04-15','YYYY-MM-DD')) and (B.channel_id = 2)
去掉,则A、B的连接将会走全表扫描

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22621861/viewspace-2081846/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-09-29

  • 博文量
    215
  • 访问量
    405482