问题描述
用了to_char() 函数之后导致的慢sql
(to_char( oofw.finish_date,‘yyyy-MM-dd’)= ‘2023-05-13’)
问题分析
select
count(t.pre_score)/count(*) znzwzqpfl,
count(t.score)/count(*) znzwzhpfl,
sum(case
when t.pre_design_step is null and t.design_step is null then 0
else 1
end)/count(*) znzwydbsjcl,
sum(case
when t.three_platform_rate is null and t.soft_probe_rate is null then 0
else 1
end)/count(*) zjhdsljcl,
count(t.iq_man_ex_result)/count(*) gdtpzjl,
t.citycode,
t.countycode,
t.cityname,
t.countyname,
t.staffid,
t.staffname,
t.year,
t.month,
t.yearandmonth
from(
select
oofw.party_name as staffname,
sta.staff_id as staffid,
to_char(oofw.finish_date, 'yyyy' )as year,
to_char(oofw.finish_date, 'MM' )as month,
to_char(oofw.finish_date, 'yyyy-MM' )as yearandmonth,
county.area_id as countycode,
oofw.county as countyname,
oofw.user_area_id as citycode,
city.area_name as cityname,
zpi.pre_score,
zpi.score,
zpi.pre_design_step,
zpi.design_step,
oofw.three_platform_rate,
oofw.soft_probe_rate,
oofw.iq_man_ex_result
from bable_a oofw
join bable_b ogcor ON oofw.order_id = ogcor.order_id
join bable_c ogco ON ogcor.group_order_id = ogco.id
join bable_d zpi ON zpi.order_id = ogco.id
left join bable_e sta on sta.staff_name = oofw.party_name
left join bable_f city on city.area_id = oofw.user_area_id
left join bable_f county on county.area_name = oofw.county
where zpi.state=1
--and to_char( oofw.finish_date,'yyyy-MM-dd') = '2023-05-13'
and oofw.finish_date >= trunc(sysdate)
) t
group by t.citycode,t.countycode,t.cityname,t.countyname,t.staffid,t.staffname,t.year,t.month,t.yearandmonth
解决方案1
1、当执行带条件只带 and oofw.finish_date >= trunc(sysdate) 条件时查询很快。
2、当执行带条件只带 and to_char( oofw.finish_date,‘yyyy-MM-dd’) = ‘2023-05-13’ 时很慢,这里表中的每一行应用一个函数to_char()到 finish_date 列上,导致不能走索引。
3、优化:改成某天时间的范围查询
把and to_char( oofw.finish_date,‘yyyy-MM-dd’) = ‘2023-05-13’ 条件换成下面这样,避免finish_date字段使用to_char()函数
and oofw.finish_date >= to_date(‘2023-05-13 00:00:00’,‘yyyy-MM-dd HH24:mi:ss’)
and oofw.finish_date <= to_date(‘2023-05-13 23:59:59’,‘yyyy-MM-dd HH24:mi:ss’)
解决方案2
to_char 优化成to_date,把"="左边函数移到右边
优化前:
select sum(POINT) as point , to_char(p.EXPIRE_DATE,‘yyyy/mm/dd’) as strParam
from MSTB_CUSTOMER p
where p.STATUS = 1
and to_char(p.EXPIRE_DATE,‘yyyymmdd’)>=to_char(sysdate,‘yyyymmdd’)
(需要优化的功能: 时间比较 , 忽略时分秒 , 只精确到日 )
优化后:
(from前面的 to_char(p.EXPIRE_DATE,‘yyyy/mm/dd’) 不影响效率)
select sum(POINT) as point , to_char(p.EXPIRE_DATE,‘yyyy/mm/dd’) as strParam
from MSTB_CUSTOMER p
where p.STATUS = 1
and EXPIRE_DATE>=to_date(to_char(sysdate,‘yyyymmdd’),‘yyyymmdd’)