1、问题项目中遇到where case when的写法如下selectcount(1)from t1 wherecasewhenstype1and cstatein(1,2)thenetimeelsewtime END BETWEEN TO_DATE(TO_CHAR(?,YYYY-MM-DD)||00:00:00,YYYY-MM-DD HH24:MI:SS)AND TO_DATE(TO_CHAR(sysdate,YYYY-MM-DD)||23:59:59,YYYY-MM-DD HH24:MI:SS)参数2026-07-01计划Case when逻辑上是“or”运算上面语句的意思是要么 stype1 and cstate in (1,2) 则选择etime要么不是stype1 and cstate in (1,2)则选择wtime因此是or运算为了让时间条件能用上索引我们可以改写成or另外or又可以转换成union all整体改写思路拆解如下首先case when stype1 and cstate in (1,2) then etime等价于(stype1 and cstate in (1,2) and etimeBETWEEN TO_DATE(TO_CHAR(?, ‘YYYY-MM-DD’) || ‘00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)AND TO_DATE(TO_CHAR(sysdate, ‘YYYY-MM-DD’) || ‘23:59:59’, ‘YYYY-MM-DD HH24:MI:SS’))然后else wtime排除stype1 and cstate in (1,2)的数据就要用wtime那就是(stype1 or cstate not in (1,2))and wtime BETWEEN TO_DATE(TO_CHAR(?, ‘YYYY-MM-DD’) || ‘00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)AND TO_DATE(TO_CHAR(sysdate, ‘YYYY-MM-DD’) || ‘23:59:59’, ‘YYYY-MM-DD HH24:MI:SS’))那么整个语句由以上两部分组成互相没有交集最终用union all进行合并。2、改写selectcount(1)from(select * from t1 a where(stype1and cstatein(1,2)and etime BETWEEN TO_DATE(TO_CHAR(?,YYYY-MM-DD)||00:00:00,YYYY-MM-DD HH24:MI:SS)AND TO_DATE(TO_CHAR(sysdate,YYYY-MM-DD)||23:59:59,YYYY-MM-DD HH24:MI:SS))union allselect* from t1 where(stype1or cstate notin(1,2))and wtime BETWEEN TO_DATE(TO_CHAR(?,YYYY-MM-DD)||00:00:00,YYYY-MM-DD HH24:MI:SS)AND TO_DATE(TO_CHAR(sysdate,YYYY-MM-DD)||23:59:59,YYYY-MM-DD HH24:MI:SS))参数2026-07-012026-07-01计划执行时间从0.381s下降到0.150s3、小结1改写用到的逻辑是case when和or、union all之间的相互转换其中注意的是如果语句能用上索引提升性能那么就可以将case when转换成or条件。2优化改写考虑是对症下药,方法很多重要的是有效。4、测试数据create table t1(id varchar(36)primary key,bcode varchar2(4),ETIMEtimestamp,WTIMEtimestamp, STYPE int,CSTATE number(2,0))partition by list(bcode)(partition p_1 values(0301), partition p_2 values(0302), partition p_3 values(0303), partition p_4 values(0304), partition p_5 values(0305), partition p_6 values(0306), partition p_7 values(0307), partition p_8 values(0308), partition p_9 values(0309), partition p_10 values(0310), partition p_11 values(0311), partition p_12 values(0312), partition p_13 values(0313), partition p_14 values(0314));insert into t1selectguid(),0||to_char(round(dbms_random.value(301,314),0)),sysdate-round(dbms_random.value(1,1000),0),sysdate-round(dbms_random.value(1,1000),0), round(dbms_random.value(1,5),0),round(dbms_random.value(1,5),0)from dual connect by level500000;commit;dbms_stats.gather_table_stats(USER,T1,null,100);create index IDX_DM_T1_ETIME on T1(ETIME)global;create index IDX_DM_T1_WTIME on T1(WTIME)global;selectcount(1)from t1 wherecasewhenstype1and cstatein(1,2)thenetimeelsewtime END BETWEEN TO_DATE(TO_CHAR(2026-01-01,YYYY-MM-DD)||00:00:00,YYYY-MM-DD HH24:MI:SS)AND TO_DATE(TO_CHAR(sysdate,YYYY-MM-DD)||23:59:59,YYYY-MM-DD HH24:MI:SS)