一个复合索引的优化案例

示例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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-2081846/