当前位置：首页 > news >正文

Where case when条件改写经验总结

news 2026/7/13 6:26:06

1、问题

项目中遇到where case when的写法，如下：

selectcount(1)from t1 wherecasewhenstype=1and 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”运算，上面语句的意思是
要么 stype=1 and cstate in (1,2) 则选择etime，要么不是stype=1 and cstate in (1,2)，则选择wtime，因此是or运算，为了让时间条件能用上索引，我们可以改写成or，另外or又可以转换成union all，整体改写思路拆解如下：
首先
case when stype=1 and cstate in (1,2) then etime
等价于
(stype=1 and cstate in (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’))
然后
else wtime，排除stype=1 and cstate in (1,2)的数据就要用wtime
那就是
(stype<>1 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(stype=1and 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(stype<>1or 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.150s

3、小结

（1）改写用到的逻辑是case when和or、union all之间的相互转换，其中注意的是如果语句能用上索引提升性能，那么就可以将case when转换成or条件。
（2）优化改写考虑是"对症下药",方法很多，重要的是有效。

4、测试数据

create table t1(id varchar(36)primary key,bcode varchar2(4),"ETIME"timestamp,"WTIME"timestamp, 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 level<=500000;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 wherecasewhenstype=1and 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')