构造例子

drop table if exists t1;
create table t1 (id int primary key,wcode varchar2(20),state int);
insert into t1 select level,'0'||to_char(round(dbms_random.value(301,400),0)),1 from dual connect by level<=100000;
commit;
insert into t1 select level+100000,'0'||to_char(round(dbms_random.value(401,900),0)),round(dbms_random.value(2,5),0) from dual connect by level<=800000;
commit;
insert into t1 select level+900000,'0'||level,3 from dual connect by level<=10;
commit;

update t1 set state=1 where wcode='03'
drop table if exists t2;
create table t2 (id int primary key,code varchar2(20),ll int);
insert into t2 select level,'0'||to_char(round(dbms_random.value(1,9),0)),2 from dual connect by level<=10;
commit;

insert into t2 select level+10,'0'||to_char(round(dbms_random.value(301,900),0)),1 from dual connect by level<=50000;
commit;

dbms_stats.gather_table_stats(USER,'T1',null,100);
dbms_stats.gather_table_stats(USER,'T2',null,100);

create index IDX_DM_t1_wcode on t1(wcode);

分析

with temp AS
(select 'BENBU' as code from t2 where ll in (2) and code ='03'
union
select code from t2 where ll in (1) and code like '03'||'%'
)select count(*) from (
select case when t1.wcode=='03' or t1.wcode like '03'||'00%' then 'BENBU' else wcode end as wcode 
,state
from t1 where (state=1)) P,temp where P.wcode like temp.code||'%'
autotrace如图：

Like关联做成笛卡尔积，右表利用不上索引，每获取驱动表一条记录，都要到右表扫描state=1的结果集，扫描右表范围比较大，每次都要case when判断，性能消耗高。
因此要解决语句性能问题，就要解决case when这一部分问题。Case when作为条件判断，如果将每一分支做成一个集合，最后汇总结果集，可以用union all去实现。语句中存在两个条件判断，所以拆分成两部分
第一分支
wcode== ‘03’ or wcode like ‘03’||’00%’
分解成：
select case when t1.wcode=='03' or t1.wcode like '03'||'00%' then 'BENBU' else wcode end as wcode
from t1 where wcode='03'
第二分支就是else 部分
那就是不等于03部分，state=1
分解成
select wcode
,state
from t1 where state=1 and wcode<>'03'
整体改写如下：

with temp AS
(select 'BENBU' as code from t2 where ll in (2) and code ='03'
union
select code from t2 where ll in (1) and code like '03'||'%'
)select sum(cnt) from(select count(1) cnt from  (
select wcode 
,state
from t1 where state=1 and wcode<>'03') P,temp where P.wcode like temp.code||'%'
union all
select count(1) from (
select case when t1.wcode=='03' or t1.wcode like '03'||'00%' then 'BENBU' else wcode end as wcode 
from t1 where wcode='03') P,temp where P.wcode = temp.code
) tt

原语句执行时间：1.7s
改写后执行时间：0.226s

总结

1、 语句中也是like case when，只不过case when放到里层去计算，实际上也是like case when。
2、 Like case when一般是拆解处理，分支拆分，能够让其利用索引，原始语句wcode上是case when计算，所以无法使用索引造成性能耗时。