weixin_42394009
2010-08-16 13:23
浏览 392

oracle数据库,SQL查询变慢

我有条sql语句如下:
select his.history_id,his.modifydate,his.organization_id,point.unitname,his.state,his.servertype
from anhuipmc.fee_history a,anhuipmc.fee_history his,anhuipmc.meshPoint_info point
where his.organization_id=trim(point.unitCode(+))
and a.user_id_ext=his.user_id_ext
and a.MODIFYDATE between to_date('2010/05/01','yyyy/mm/dd') and to_date('2010/05/30','yyyy/mm/dd') and a.state='1'
and his.MODIFYDATE between to_date('2010/06/01','yyyy/mm/dd') and to_date('2010/06/30','yyyy/mm/dd') and his.state='2'
and not exists(select 1 from anhuipmc.fee_history b where his.user_id_ext=b.user_id_ext
and b.MODIFYDATE between to_date('2010/05/01','yyyy/mm/dd') and to_date('2010/05/30','yyyy/mm/dd') and b.state='2')
and his.organization_id = '340101002'

我分别在modifydate,organization_id,state,user_id_ext,accountnumber上建立过索引另外建立过一个联合索引(modifydate,organization_id,state)
现在发现不在union all上下2条语句中添加and his.organization_id ='340101002'这个where条件,速度还是可以接受的,但是加了后就非常非常慢,结果几分钟都查不出来求各位牛人帮忙看看是什么原因.
本人数据库是oracle10g,表记录大概有接近700w.
另外有个疑问:如果要建联合索引的话是不是要把子查询里面的where条件中涉及的字段也加上啊?
求教各位,本人拜谢!!

这条sql只是完整的sql语句中的一部分因为是用union all 连接的所以单独测了下是这条语句暴慢,完整的如下,想看的可以看看:

select count(*) from(
select organization_id,unitname,state,servertype,count(*) count from(
select distinct(history_id) history_id, organization_id,unitname,state,servertype from (
select his.history_id,his.modifydate,his.organization_id,point.unitname,his.state,his.servertype
from anhuipmc.fee_history his,anhuipmc.yxt_user_info y,anhuipmc.meshPoint_info point
where his.organization_id=trim(point.unitCode(+))
and his.accountnumber=y.accountnumber
and his.modifydate>y.subscribedate+(select case c.chargetype when '01' then 60 when '02' then 60 else 0 end from anhuipmc.fee_history c where c.history_id=his.history_id)
and his.modifydate<y.subscribedate+(select case c.chargetype when '01' then 90 when '02' then 90 else 0 end from anhuipmc.fee_history c where c.history_id=his.history_id)
and his.MODIFYDATE between to_date('2010/06/01','yyyy/mm/dd') and to_date('2010/06/30','yyyy/mm/dd') and his.state='2'

and his.organization_id = '340101002'
union all
select his.history_id,his.modifydate,his.organization_id,point.unitname,his.state,his.servertype
from anhuipmc.fee_history a,anhuipmc.fee_history his,anhuipmc.meshPoint_info point
where his.organization_id=trim(point.unitCode(+))
and a.user_id_ext=his.user_id_ext
and a.MODIFYDATE between to_date('2010/05/01','yyyy/mm/dd') and to_date('2010/05/30','yyyy/mm/dd') and a.state='1'
and his.MODIFYDATE between to_date('2010/06/01','yyyy/mm/dd') and to_date('2010/06/30','yyyy/mm/dd') and his.state='2'
and not exists(select 1 from anhuipmc.fee_history b where his.user_id_ext=b.user_id_ext
and b.MODIFYDATE between to_date('2010/05/01','yyyy/mm/dd') and to_date('2010/05/30','yyyy/mm/dd') and b.state='2')
and his.organization_id = '340101002'
)
)
group by organization_id,unitname,state,servertype )

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

7条回答 默认 最新

  • 「已注销」 2010-08-16 14:59
    已采纳

    优化sql有好几条规则,像count(*)这了,避免用DISTINCT了。等等,这也有说的很好的帖子,你对比着优化一下![url]http://database.51cto.com/art/200904/118526.htm[/url]

    点赞 评论
  • 「已注销」 2010-08-16 14:45

    你的sql语句我大概看了一下,一开始就发现count(*),这是不提倡的,建议count(id),用一个查询出来的字段。

    点赞 评论
  • 「已注销」 2010-08-16 15:01

    模糊匹配会导致索引无法使用。

    点赞 评论
  • cuiran 2010-08-16 15:36

    UNION ALL 会将符合条件的都列出,不论是否重复。所以效率会低.

    点赞 评论
  • 「已注销」 2010-08-16 16:13

    你这有
    his.organization_id=trim(point.unitCode(+))

    and his.organization_id = '340101002'

    这不对吧?建议把上面那个去掉,还有你语句里还有count(*)

    点赞 评论
  • 「已注销」 2010-08-16 16:40

    and his.organization_id ='340101002' 你的意思不就是加上这个才慢的吗?我的意思是。
    你有两个。
    his.organization_id=trim(point.unitCode(+))

    and his.organization_id = '340101002'

    而且还在一个where里,就是一个查询,不识字查询,那么肯定只用and his.organization_id ='340101002'这个条件就ok了。and his.organization_id ='340101002'这个条件直接固定了and his.organization_id 必须是'340101002',那么你加his.organization_id=trim(point.unitCode(+))就没有意义了吧?

    点赞 评论
  • 「已注销」 2010-08-17 09:52

    是啊,join in影响速度的。我也是一直这么认为的。难道理解错了,还是使用场景不一样也不一样。还有我老感觉你那两个his.organization_id=trim(point.unitCode(+))

    and his.organization_id = '340101002'

    别扭。。。呵呵。

    点赞 评论

相关推荐 更多相似问题