麻烦帮忙优化下这条sql,数据量太大,查的实在太慢

蓝调_Z 2015-06-05 03:41:03

select count(1)
from (select a.id, c.startdate, c.finishdate
from T_LAW_CHECKLOG a, all_flowid b, rs_wf_entry c
where a.id = b.rcjc_id(+)
and b.rcjc_fid = c.id(+)
and c.finishdate is not null
and c.finishdate >= c.startdate
and to_date(substr(a.creatime, 0, 8), 'yyyymmdd') >=
to_date('2015-01-01', 'yyyy-mm-dd')
and to_date(substr(a.creatime, 0, 8), 'yyyymmdd') <=
to_date('2015-06-01', 'yyyy-mm-dd')
UNION ALL
select distinct b.id, c.startdate, c.finishdate
from T_LAW_CHECKLOG a, T_LAW_REPORT b, rs_wf_entry c
where a.REPORT_ID = b.id(+)
and b.PROCESSINSTID = c.id(+)
and c.finishdate is not null
and c.finishdate >= c.startdate
and to_date(substr(b.report_time, 0, 8), 'yyyymmdd') >=
to_date('2015-01-01', 'yyyy-mm-dd')
and to_date(substr(b.report_time, 0, 8), 'yyyymmdd') <=
to_date('2015-06-01', 'yyyy-mm-dd')
UNION ALL
select d.id, c.startdate, c.Finishdate
from T_LAW_CHECKLOG a,
T_LAW_REGISTER b,
T_LAW_DISPOSE d,
rs_wf_entry c
where d.REGISTER_ID = b.id(+)
and b.CHK_ID = a.id(+)
and d.cfjd_entry_id = c.id(+)
and instr(d.wf_law_ids, '402880c14389691901438a6c384200b9') > 0
and c.Finishdate is not null
and c.startdate <= c.Finishdate
and to_date(substr(d.CREATIME, 0, 8), 'yyyymmdd') >=
to_date('2015-01-01', 'yyyy-mm-dd')
and to_date(substr(d.CREATIME, 0, 8), 'yyyymmdd') <=
to_date('2015-06-01', 'yyyy-mm-dd')) al
...全文
222 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Tiger_Zhao 2015-06-10
  • 打赏
  • 举报
回复
几个比较日期范围的字段 creatime、report_time 难道不是字符类型?
加快速度需要有以下索引:
T_LAW_CHECKLOG(creatime)包含(id,REPORT_ID)
all_flowid(rcjc_id)
rs_wf_entry(id)包含(startdate,finishdate)
T_LAW_REPORT(report_time)包含(id,PROCESSINSTID)
T_LAW_REGISTER(CHK_ID)包含(id)
T_LAW_DISPOSE(CREATIME)包含(REGISTER_ID,cfjd_entry_id,wf_law_ids)


第二部分 distinct b.id 和 distinct b.id, c.startdate, c.finishdate 的数量比应该是 <= 啊,怎么会多呢?
你把3个部分分开来比较,哪个数量有差异?
额44丰富 2015-06-10
  • 打赏
  • 举报
回复
你可以改成with as生成临时表,这几张表都扫描了多次
蓝调_Z 2015-06-09
  • 打赏
  • 举报
回复
引用 4 楼 Tiger_Zhao 的回复:
select SUM(n)
  from (select count(1) as n
          from T_LAW_CHECKLOG a, all_flowid b, rs_wf_entry c
         where a.id = b.rcjc_id(+)
           and b.rcjc_fid = c.id(+)
           and c.finishdate is not null
           and c.finishdate >= c.startdate
           and a.creatime >= '20050101'
           and a.creatime < '20150602'
        UNION ALL
        select count(distinct b.id) as n
          from T_LAW_CHECKLOG a, T_LAW_REPORT b, rs_wf_entry c
         where a.REPORT_ID = b.id(+)
           and b.PROCESSINSTID = c.id(+)
           and c.finishdate is not null
           and c.finishdate >= c.startdate
           and b.report_time >= '20050101'
           and b.report_time < '20150602'
        UNION ALL
        select count(1) as n
          from T_LAW_CHECKLOG a,
               T_LAW_REGISTER b,
               T_LAW_DISPOSE  d,
               rs_wf_entry    c
         where d.REGISTER_ID = b.id(+)
           and b.CHK_ID = a.id(+)
           and d.cfjd_entry_id = c.id(+)
           -- 下面这个性能太差,能不能改成直接比较?
           and instr(d.wf_law_ids, '402880c14389691901438a6c384200b9') > 0
           and c.Finishdate is not null
           and c.startdate <= c.Finishdate
           and d.CREATIME >= '20050101'
           and d.CREATIME < '20150602'
       ) al
谢了,我刚试了下,执行时间差不多,而且莫名的数据还多了十多万条
Tiger_Zhao 2015-06-08
  • 打赏
  • 举报
回复
select SUM(n)
from (select count(1) as n
from T_LAW_CHECKLOG a, all_flowid b, rs_wf_entry c
where a.id = b.rcjc_id(+)
and b.rcjc_fid = c.id(+)
and c.finishdate is not null
and c.finishdate >= c.startdate
and a.creatime >= '20050101'
and a.creatime < '20150602'
UNION ALL
select count(distinct b.id) as n
from T_LAW_CHECKLOG a, T_LAW_REPORT b, rs_wf_entry c
where a.REPORT_ID = b.id(+)
and b.PROCESSINSTID = c.id(+)
and c.finishdate is not null
and c.finishdate >= c.startdate
and b.report_time >= '20050101'
and b.report_time < '20150602'
UNION ALL
select count(1) as n
from T_LAW_CHECKLOG a,
T_LAW_REGISTER b,
T_LAW_DISPOSE d,
rs_wf_entry c
where d.REGISTER_ID = b.id(+)
and b.CHK_ID = a.id(+)
and d.cfjd_entry_id = c.id(+)
-- 下面这个性能太差,能不能改成直接比较?
and instr(d.wf_law_ids, '402880c14389691901438a6c384200b9') > 0
and c.Finishdate is not null
and c.startdate <= c.Finishdate
and d.CREATIME >= '20050101'
and d.CREATIME < '20150602'
) al
蓝调_Z 2015-06-05
  • 打赏
  • 举报
回复
数据量十万级至百万级
蓝调_Z 2015-06-05
  • 打赏
  • 举报
回复
引用 1 楼 wildwave 的回复:
贴出执行计划 介绍下相关表和字段的一些统计信息,例如数据量,选择性
直接根据传入的时间段查数据就ok了,就是我目前写死的那几个两个时间
小灰狼W 2015-06-05
  • 打赏
  • 举报
回复
贴出执行计划 介绍下相关表和字段的一些统计信息,例如数据量,选择性

3,491

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧