怎么避免to_char() 函数之后导致的慢sql

问题描述

用了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’)