这个SQL语句可以怎么优化，救急

[yzf86211861]

select case when TO_CHAR(a.CREATETIME,'HH24MI') < 0730 THEN TO_CHAR(a.CREATETIME - 1,'YYYYMMDD')
else TO_CHAR(a.CREATETIME ,'YYYYMMDD')
end as work_date,
a.workshop,
sum(case when B.SUBMATERIALQTY = 32 then 1 else 0 end ) as EL_QTY32
from PM_REPAIR_MODULE a,
cim_lot b
where a.createtime >= (select LAST_DAY(ADD_MONTHS(TO_DATE('20190805', 'yyyymmdd hh24:mi:ss'),-1)) from dual)
and a.createtime < to_date('20190805' ||'073000','YYYYMMDD HH24MISS')+ 1
and a.location_flag = 'FRONT'
and a.rework_seq = '1'
and a.lotid = b.lotid
group by case when TO_CHAR(a.CREATETIME,'HH24MI') < 0730 THEN TO_CHAR(a.CREATETIME - 1,'YYYYMMDD')
else TO_CHAR(a.CREATETIME ,'YYYYMMDD')
end, a.workshop

请教一下 这个语句怎么优化，
红色部分 是取上个月的最后一天 ，我直接写成 ‘20190731’去查，速度还是挺快的7秒，
写成这个用语句计算出来上个月最后一天之后查询需要4分钟.

[AHUA1001]

select case when TO_CHAR(a.CREATETIME,'HH24MI') < 0730 THEN TO_CHAR(a.CREATETIME - 1,'YYYYMMDD') else TO_CHAR(a.CREATETIME ,'YYYYMMDD') end as work_date, a.workshop, (SELECT sum(case when B.SUBMATERIALQTY = 32 then 1 else 0 end ) FROM cim_lot b WHERE b.lotid = a.lotid) as EL_QTY32 from PM_REPAIR_MODULE a where a.createtime >= TRUNC(TO_DATE('20190805', 'yyyymmdd hh24:mi:ss'),'MM')-1 and a.createtime < to_date('20190805' ||'073000','YYYYMMDD HH24MISS')+ 1 and a.location_flag = 'FRONT' and a.rework_seq = '1' group by case when TO_CHAR(a.CREATETIME,'HH24MI') < 0730 THEN TO_CHAR(a.CREATETIME - 1,'YYYYMMDD') else TO_CHAR(a.CREATETIME ,'YYYYMMDD') end, a.workshop

[nayi_224]

a.createtime >= LAST_DAY(ADD_MONTHS(TO_DATE('20190805', 'yyyymmdd'),-1))

[minsic78]

Oracle在计算一些函数表达式选择率的时候，是有些不太合理，直接添加提示试试吧： /*+leading(a) use_nl_with_index(b)*/ 不过那个时间条件明明可以不用从dual表select的，写成那样是什么目的？

[stelf]

trunc(TO_DATE('20190805', 'yyyymmdd'), 'mm') - 1 这个方式也可以获取上个月最后一天。 首先看一下 createtime字段是否有索引，在这个日期范围内的数据总量有多少。lot_id是否有主外键或者索引？ 就你这个语句来看，case when 应该没有意义。你这是olap还是oltp系统，7秒钟时间也挺久的？如果是数仓类的话，是否可以提前把数据计算好等等