Or子句改为Union ALL之后，成本减低

昨晚测试一个SQL语句优化，发现一个地方，大家以后可以注意
select t4.area_code, t4.area_name,t3.data_item_code,sum(t3.data_item_value) as
data_item_value    
 from PF_IndicatorDataItem t1,PF_StatDataItem t2,PF_StatDataItemValue
t3,PF_StatValueAttatchedInfo t4    
 where t1.DATA_ITEM_CODE=t2.DATA_ITEM_CODE and
t2.DATA_ITEM_CODE=t3.DATA_ITEM_CODE      and t3.NODE_ID=t4.NODE_ID and
t3.DAY=t4.DAY    
 and (t3.obtain_mode=2 or ( t3.obtain_mode =1     and  t4.WEEK >='2013-35' and
t4.WEEK <='2013-35'   and   t1.indicator_id =1  and  t4.node_type_id =4  and
t4.area_code in  ( '330281', '330282', '330283', '330226', '330225', '330212'
)  ) )    
 group by t4.area_code, t4.area_name,t3.data_item_code  ;
这个语句带有Or子句，成本1万多
把Or子句改为Union ALL之后，成本减低
// Union Al
select t5.area_code, t5.area_name,t5.data_item_code,sum(t5.data_item_value)
        as data_item_value
from
(select t4.area_code, t4.area_name,t3.data_item_code, t3.data_item_value
from PF_IndicatorDataItem t1,PF_StatDataItem t2,PF_StatDataItemValue t3,
        PF_StatValueAttatchedInfo t4
where t1.DATA_ITEM_CODE=t2.DATA_ITEM_CODE and
        t2.DATA_ITEM_CODE=t3.DATA_ITEM_CODE and t3.NODE_ID=t4.NODE_ID and
        t3.DAY=t4.DAY and (t3.obtain_mode =1 and t4.WEEK
        >='2013-35' and t4.WEEK <='2013-35' and t1.indicator_id =1 and
        t4.node_type_id =4 and t4.area_code in ('330281', '330282', '330283',
        '330226', '330225', '330212'))
UNION ALL
select t4.area_code, t4.area_name,t3.data_item_code, t3.data_item_value
from PF_IndicatorDataItem t1,PF_StatDataItem t2,PF_StatDataItemValue t3,
        PF_StatValueAttatchedInfo t4
where t1.DATA_ITEM_CODE=t2.DATA_ITEM_CODE and
        t2.DATA_ITEM_CODE=t3.DATA_ITEM_CODE and t3.NODE_ID=t4.NODE_ID and
        t3.DAY=t4.DAY and t3.obtain_mode=2)  as t5
group by t5.area_code, t5.area_name,t5.data_item_code;
船长（王飞鹏）(16198686)  9:14:55
UNION ALL之后成本只有4000

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

转载于:http://blog.itpub.net/241379/viewspace-772179/