oracle复合索引使用案例,一个复合索引的优化案例

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;