一、构造测例

drop table if exists tt1;   
create table tt1 (id varchar2(20) primary key,code varchar2(4),c2 varchar2(20),ll int);
insert into tt1 select 'A'||level,'0'||to_char(round(dbms_random.value(300,320),0)),'C'||to_char(round(dbms_random.value(1,5000),0)),2
from dual connect by level<=20000;
commit;  
insert into tt1 select 'A'||level+200000,'03','C'||to_char(round(dbms_random.value(1,5000),0)),1
from dual connect by level<=100;
commit; 

drop table if exists tt2;   
create table tt2 (id varchar2(20) primary key,code varchar2(4),ll int);
insert into tt2 select 'A'||level,'0'||to_char(round(dbms_random.value(1,10),0)),2
from dual connect by level<=10;
commit;

insert into tt2 select 'A'||level+10000,'0'||to_char(round(dbms_random.value(300,320),0)),2
from dual connect by level<=10;
commit; 

dbms_stats.gather_table_stats(USER,'TT1',null,100);
dbms_stats.gather_table_stats(USER,'TT2',null,100);  
create index IDX_DM_TT1_CODE on tt1(code);
create index IDX_DM_TT2_CODE on tt2(code);

select count(*)
  from tt1, tt2
 where (tt1.code like case
         when tt1.ll = 1 then
          tt2.code
         when tt1.ll = 2 then
          tt2.code || '%'
       end)；
执行时间：3.7s
计划：

1   #NSET2: [8652, 1, 100] 
2     #PRJT2: [8652, 1, 100]; exp_num(1), is_atom(FALSE) 
3       #AAGR2: [8652, 1, 100]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4         #SLCT2: [8652, 200100, 100]; TT1.CODE LIKE exp_bool_case
5           #NEST LOOP INNER JOIN2: [8652, 200100, 100] 
6             #SSCN: [1, 20, 48]; IDX_DM_TT2_CODE(TT2); btr_scan(1); is_global(0)
7             #CSCN2: [23, 200100, 52]; INDEX33679075(TT1); btr_scan(1)

复制

二、分析
语句中的性能问题在于like case when做成笛卡尔积。
case when用于条件判断，语句用业务角度理解，假设ll是层级的意思，第一（基层）层级就获取本级别的数据；第二层级有权限获取下级的数据，做like关联获取本级及下级数据。最终展示两个层级的所有数据，就是层级1+层级2数据，因此从这个角度分析可以用union all来改写。

改写方案1
with temp as
(
select * from tt1 where ll in (1,2)
)
select sum(cnt) from 
(
select count(1) cnt from tt1,tt2 where tt1.ll=1 and tt1.code=tt2.code
union all
select count(1) cnt from tt1,tt2 where tt1.ll=2 and tt1.code like tt2.code||'%'
);
执行时间：0.199s
计划：
1   #NSET2: [180, 1, 100] 
2     #PRJT2: [180, 1, 100]; exp_num(1), is_atom(FALSE) 
3       #AAGR2: [180, 1, 100]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4         #PRJT2: [180, 2, 100]; exp_num(1), is_atom(FALSE) 
5           #UNION ALL: [180, 2, 100]
6             #PRJT2: [26, 1, 100]; exp_num(1), is_atom(FALSE) 
7               #AAGR2: [26, 1, 100]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
8                 #HASH2 INNER JOIN: [26, 153, 100];  KEY_NUM(1); KEY(TT2.CODE=TT1.CODE) KEY_NULL_EQU(0)
9                   #SSCN: [1, 20, 48]; IDX_DM_TT2_CODE(TT2); btr_scan(1); is_global(0)
10                  #SLCT2: [25, 100, 52]; TT1.LL = 1
11                    #CSCN2: [25, 200100, 52]; INDEX33679075(TT1); btr_scan(1)
12            #PRJT2: [154, 1, 100]; exp_num(1), is_atom(FALSE) 
13              #AAGR2: [154, 1, 100]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
14                #SLCT2: [154, 150060, 100]; TT1.LL = 2
15                  #NEST LOOP INDEX JOIN2: [154, 150060, 100] 
16                    #SSCN: [1, 20, 48]; IDX_DM_TT2_CODE(TT2); btr_scan(1); is_global(0)
17                    #BLKUP2: [153, 7503, 48]; IDX_DM_TT1_CODE(TT1)
18                      #SSEK2: [153, 7503, 48]; scan_type(ASC), IDX_DM_TT1_CODE(TT1), scan_range[exp11,exp11), is_global(0)

复制

另外条件判断，要么满足条件1，要么满足条件2，or运算也属于条件判断，那么case when也可以改写成or。

改写方案2：
select 
 count(*)
  from tt1, tt2
 where (tt1.ll = 1 and tt1.code = tt2.code)
    or (tt1.ll = 2 and tt1.code like tt2.code || '%')
    
执行时间：0.199s
计划：
1   #NSET2: [196, 1, 124] 
2     #PRJT2: [196, 1, 124]; exp_num(1), is_atom(FALSE) 
3       #AAGR2: [196, 1, 124]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
4         #UNION FOR OR2: [196, 150213, 124]; key_num(2), outer_join(-)
5           #HASH2 INNER JOIN: [26, 153, 124];  KEY_NUM(1); KEY(TT2.CODE=TT1.CODE) KEY_NULL_EQU(0)
6             #SSCN: [1, 20, 60]; IDX_DM_TT2_CODE(TT2); btr_scan(1); is_global(0)
7             #SLCT2: [25, 100, 64]; TT1.LL = 1
8               #CSCN2: [25, 200100, 64]; INDEX33679075(TT1); btr_scan(1)
9           #SLCT2: [156, 150060, 124]; TT1.LL = 2
10            #NEST LOOP INDEX JOIN2: [156, 150060, 124] 
11              #SSCN: [1, 20, 60]; IDX_DM_TT2_CODE(TT2); btr_scan(1); is_global(0)
12              #BLKUP2: [156, 7503, 48]; IDX_DM_TT1_CODE(TT1)
13                #SSEK2: [156, 7503, 48]; scan_type(ASC), IDX_DM_TT1_CODE(TT1), scan_range[exp11,exp11), is_global(0)

复制

三、总结
语句where中出现case when判断，一般可以改写成or方式和union all方式，另外在等价改写中要注意等价替换，确保逻辑上是成立。