# 案例：改写SQL消除in-clause
#### 优化前
in-clause/any-clause是常见的SQL语句约束条件，有时in或any后面的clause都是常量，类似于：
```
select 
count(1) 
from calc_empfyc_c1_result_tmp_t1 
where ls_pid_cusr1 in ('20120405','20130405');
```
或者
```
select 
count(1) 
from calc_empfyc_c1_result_tmp_t1 
where ls_pid_cusr1 in any('20120405','20130405');
```
但是也有一些如下的特殊用法：
```
SELECT 
ls_pid_cusr1,COALESCE(max(round((current_date-bthdate)/365)),0)
FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2
WHERE t1.ls_pid_cusr1 = any(values(id),(id15))
GROUP BY ls_pid_cusr1;
```
其中，id、id15为p10_md_tmp_t2中的两列，"t1.ls_pid_cusr1 = any(values(id), (id15))*"* 等价于"t1.ls_pid_cusr1 = id or t1.ls_pid_cusr1 = id15*"。*
因此join-condition实质上是一个不等式，这种不等值的join操作必须走nestloop，对应执行计划如下：
![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000001188163812.png "点击放大")
#### 优化后
测试发现由于两表结果集过大，导致nestloop耗时过长，超过一小时未返回结果，因此性能优化的关键是消除nestloop，让join走更高效的hashjoin。从语义等价的角度消除any-clause，SQL改写如下：
```
select
ls_pid_cusr1,COALESCE(max(round(ym/365)),0)
from
(
         (
                   SELECT 
                            ls_pid_cusr1,(current_date-bthdate) as ym
                   FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2
                   WHERE t1.ls_pid_cusr1 = t2.id and t1.ls_pid_cusr1 != t2.id15
         )
         union all
         (
                   SELECT 
                            ls_pid_cusr1,(current_date-bthdate) as ym
                   FROM calc_empfyc_c1_result_tmp_t1 t1,p10_md_tmp_t2 t2
                   WHERE t1.ls_pid_cusr1 = id15
         )
)
GROUP BY ls_pid_cusr1;
```
注意：尽量使用union all代替union。union在合并两个集合时会执行去重操作，而union all则直接将两个结果集合并、不执行去重。执行去重会消耗大量的时间，因此，在一些实际应用场景中，如果通过业务逻辑已确认两个集合不存在重叠，可用union all替代union以便提升性能。
优化后的SQL查询由两个等值join的子查询构成，而每个子查询都可以走更适合此场景的hashjoin。优化后的执行计划如下
![](https://support.huaweicloud.com/devg-dws/figure/zh-cn_image_0000001233761929.jpg "点击放大")
优化后，从超过1个小时未返回结果优化到7s返回结果。