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
------解决思路----------------------
贴出执行计划
介绍下相关表和字段的一些统计信息，例如数据量，选择性
------解决思路----------------------
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
------解决思路----------------------
几个比较日期范围的字段 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个部分分开来比较，哪个数量有差异？