我有条sql语句如下:
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 ) 我分别在modifydate,organization_id,state,user_id_ext,accountnumber上建立过索引,另外建立过一个联合索引(modifydate,organization_id,state)
现在发现不在union all上下2条select语句中添加"and his.organization_id ='340101002'"这个where条件,速度还是可以接受的,但是加了后就非常非常慢,结果几分钟都查不出来.
求各位牛人看看是什么原因.因为这个条件是拼装出来的随意未添加到联合索引中只单独加了索引.
本人数据库是oracle10g,表记录大概有接近700w.
另外有个疑问:如果要建联合索引的话是不是要把子查询里面的where条件中涉及的字段也加上啊还是说子查询的索引归子查询,外面的where中的字段合一起建立索引?
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 ) 我分别在modifydate,organization_id,state,user_id_ext,accountnumber上建立过索引,另外建立过一个联合索引(modifydate,organization_id,state)
现在发现不在union all上下2条select语句中添加"and his.organization_id ='340101002'"这个where条件,速度还是可以接受的,但是加了后就非常非常慢,结果几分钟都查不出来.
求各位牛人看看是什么原因.因为这个条件是拼装出来的随意未添加到联合索引中只单独加了索引.
本人数据库是oracle10g,表记录大概有接近700w.
另外有个疑问:如果要建联合索引的话是不是要把子查询里面的where条件中涉及的字段也加上啊还是说子查询的索引归子查询,外面的where中的字段合一起建立索引?
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'
如果不加and his.organization_id = '340101002' 查询速度大概14秒加了就不知道时间了
这个我也考虑过可是想不出有啥方法可以取代not exists.
举个业务逻辑例子:选出所有6月份有扣费成功(state为2)但是5月份所有扣费全部失败(state为1)的记录
嗯,重新看了下虽然不是这个问题的范畴但是其他sql语句还是可以优化下,谢谢提醒
his.organization_id=trim(point.unitCode(+))是连接条件啊,因为要选point.unitname字段
select count(*) from( ) where organization_id = '340101002'
把from(......)里的organization_id = '340101002'去掉。