ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Or子句改为Union ALL之后,成本减低

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

原创 Linux操作系统 作者:47328983 时间:2013-09-05 09:17:28 0 删除 编辑
昨晚测试一个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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Errpt
请登录后发表评论 登录
全部评论

注册时间:2009-03-07

  • 博文量
    111
  • 访问量
    331789