我有条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 )
