我有条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中的字段合一起建立索引?
解决方案 »
- 统计分析后查询变慢
- 菜鸟:欲用脚本从oracle 10g升级到11g,出现问题!
- 求解:一个求和的 SQL
- ORACLE816出现乱码
- 双机热备的服务器在磁盘阵列柜中装ORACLE,切换服务器主机时Oracle数据库无法打开
- 安装ORACLE8i的时候,点了setup后,没有任何反应,这是怎么回事啊??????
- "slect * from tableName select @@ROWCOUNT as DataRowCount"在oracle 中怎么写!!
- 创建表时遇到的麻烦
- 如何创建一个非空的自动加一的字段(在线等待,解决即结贴)
- 请教大神,如何通过sql语句处理一个具有等价关系的表?
- 命名块遇到错误
- 怎样删除accept输入的变量
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'去掉。