【干货攻略】SQL优化之-LIKE CASE WHEN改写思路

达梦E学 2024-11-26

465

引言

条件判断case when与union all之间的转换思考。

本章内容已在如下环境上测试：

①数据库版本：达梦DM8。

相关关键字：SQL优化，case when。

——正文——

构造例子

SQL> create table t1 (id int primary key,wcode varchar2(20),state int);
SQL> insert into t1 select level,'0'||to_char(round(dbms_random.value(301,400),0)),1 from  dual connect by level<=100000;
SQL> 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;
SQL> insert into t1 select level+900000,'0'||level,3 from dual connect by level<=10;
SQL> update t1 set state=1 where wcode='03'；
SQL> commit;
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;
insert into t2 select level+10,'0'||to_char(round(dbms_random.value(301,900),0)),1 from dual connect by level<=50000;
SQL> 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);

分析

SQL> 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||'%';
/

执行结果如下：

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'

整体改写如下：

SQL> 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;
/