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

[蓝调_Z]

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

[小灰狼W]

贴出执行计划 介绍下相关表和字段的一些统计信息，例如数据量，选择性

[蓝调_Z]

引用 1 楼 wildwave 的回复:

贴出执行计划 介绍下相关表和字段的一些统计信息，例如数据量，选择性

直接根据传入的时间段查数据就ok了，就是我目前写死的那几个两个时间

[蓝调_Z]

数据量十万级至百万级

[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

[蓝调_Z]

引用 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

谢了，我刚试了下，执行时间差不多，而且莫名的数据还多了十多万条

[额44丰富]

你可以改成with as生成临时表，这几张表都扫描了多次

[Tiger_Zhao]

几个比较日期范围的字段 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个部分分开来比较，哪个数量有差异？