同样的查询语句在同一个数据库内，只有日期不同，但是执行时间差很多，查询当天的数据会慢很多，是什么原因？

lijun900312 2018-07-25 10:01:10

wselect (select wm_concat(c.carno)
from es_carinfo c
where c.carcode = z.kchp
and c.EXAMPOINTID = z.kdid) carno,
z.id,
z.kfxm,
case
when z.jxmc is null then
'自培'
else
z.jxmc
end jxmc,
z.zt,
z.kskm,
z.xm,
z.sfzmhm,
z.kscx,
z.kscj,
z.kscs,
to_char(z.ksrq, 'yyyy-mm-dd') ksrq,
z.hg,
z.batflag,
z.flag,
z.kskssj,
z.name,
z.ksyxm,
z.sjkg,
z.kchp,
z.ksyy,
z.ksxl,
z.yycs,
z.car_type,
case
when z.checknum >= 2 then
'是'
else
'否'
end checknum
from (select (t.lsh || '_' || to_char(t1.ksrq, 'yyyymmdd') || '_' ||
t1.kskm) id,
t.kfxm,
case
when t2.jxmc is null then
t1.dlr
else
t2.jxmc
end jxmc,
t1.xm,
t3.name,
t1.kskm,
t1.sfzmhm,
t4.xm ksyxm,
t1.kscx,
max(t.kchp) kchp,
max(t.kscj) kscj,
count(1) kscs,
to_char(t1.ksrq, 'yyyymmdd'),
max(t.hg) hg,
max(t.kskssj) kskssj,
max(t.batflag) batflag,
t1.ksrq,
t1.zt,
case
when t1.zt = '0' then
'未校验'
else
decode(to_number(t1.zt) - decode(max(t.hg), '0', 2, '1', 1),
0,
'通过',
'<p style=background-color:#FF0000>未通过</p>')
end flag,
sum(t.checknum) checknum,
case
when t5.xm is not null then
t5.xm
else
t4.xm
end sjkg,
ksyy,
t1.ksxl,
t1.yycs,
t1.car_type,
t1.kdid
from (select t.lsh,
t.ksrq,
t.kskm,
t.kchp,
t.hg,
t.batflag,
t.kskssj,
t.kscj,
null kfxm,
case
when t.ISKSYCHECK = '0' then
0
when t.ISKSYCHECK = '1' and t.hg = '0' then
1
when t.ISKSYCHECK = '1' and t.hg = '1' then
2
else
0
end checknum
from es_exam_grade t
where 1 = 1
and ((t.sn != 'invalid score' and t.sn != 'day') or
t.sn is null)
and (t.ISKSYCHECK is null or t.ISKSYCHECK != '2')
and t.kskssj >=
to_date('2018-07-24 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.kskssj <=
to_date('2018-07-24 23:59:59', 'yyyy-mm-dd hh24:mi:ss')) t
inner join (select lsh,
ykrq ksrq,
kskm,
dlr,
ksdd,
ksy1,
ksy2sfzmhm,
zt,
kscx,
sfzmhm,
xm,
ksyy,
ksxl,
yycs,
car_type,
t1.id kdid
from es_drvpreasign t
inner join ES_EXAMPOINTDETAIL t1
on t.kskm = t1.type
and t.ksdd = t1.code
where 1 = 1
and ykrq >= to_date('2018-07-24 00:00:00',
'yyyy-mm-dd hh24:mi:ss')
and ykrq <= to_date('2018-07-24 23:59:59',
'yyyy-mm-dd hh24:mi:ss')
and t1.id in (2)) t1
on t.lsh = t1.lsh
and t.ksrq = t1.ksrq
and t.kskm = t1.kskm
left join ES_SCHOOLINFO t2
on t1.dlr = t2.jxdm
left join ES_EXAMPOINTDETAIL t3
on t1.KSDD = t3.code
and t1.KSKM = t3.type
left join (select t.xm, t.SFZMHM
from ES_EXAMMANINFO t
group by t.xm, t.SFZMHM) t4
on t1.ksy1 = t4.SFZMHM
left join ES_EXAMMANINFO t5
on t5.sfzmhm = t1.ksy2sfzmhm
where 1 = 1
group by t.lsh,
t.kfxm,
t1.xm,
t1.ksrq,
t1.kskm,
t2.jxmc,
t3.name,
t1.sfzmhm,
t4.xm,
t1.kscx,
t1.zt,
t1.ksrq,
t5.xm,
ksyy,
t1.ksxl,
t1.yycs,
t1.car_type,
t1.dlr,
t1.kdid) z
where 1 = 1
order by z.kskssj desc

...全文