我有条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 )
优化sql有好几条规则，像count(*)这了，避免用DISTINCT了。等等，这也有说的很好的帖子，你对比着优化一下！[url]http://database.51cto.com/art/200904/118526.htm[/url]
你的sql语句我大概看了一下，一开始就发现count(*)，这是不提倡的，建议count(id)，用一个查询出来的字段。
模糊匹配会导致索引无法使用。
UNION ALL 会将符合条件的都列出,不论是否重复。所以效率会低.
你这有
his.organization_id=trim(point.unitCode(+))
and his.organization_id = '340101002'
这不对吧？建议把上面那个去掉，还有你语句里还有count(*)
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(+))就没有意义了吧？
是啊，join in影响速度的。我也是一直这么认为的。难道理解错了，还是使用场景不一样也不一样。还有我老感觉你那两个his.organization_id=trim(point.unitCode(+))
and his.organization_id = '340101002'
别扭。。。呵呵。