一個複合索引的優化案例

hooca發表於2016-04-15
索引優化
示例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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章