in 子查询 or in 子查询改写

1、 问题语句

select t1.c1,t1.c2,t1.c3 from t1 where(t1.gid in(select gid from t2 where d1 ='C2')or t1.gid in(select gid from t3 where e1 ='C2'))and(t1.c1 ='C4'or t1.c1 is null)and(t1.c2 liketrim('D4%')or t1.c3 liketrim('E7%'));

计划：

这里的in子查询+or+in子查询做成表达式，无法先过滤，这里in子查询里面的条件过滤性较好，可以利用索引先过滤，之前的文章中有提过or条件可以改写成union all，这里可以考虑改写成union all。

2、改写

select t1.c1,t1.c2,t1.c3 from t1 where(t1.gid in(select gid from t2 where d1 ='C2'union all select gid from t3 where e1 ='C2'))and(nvl(t1.c1,'C4')='C4')and(t1.c2 liketrim('D4%')or t1.c3 liketrim('E7%'));

计划：

这里能够很好的利用索引先过滤，这里能够很好的利用索引先过滤，另外in也可以改写成exists，于是这个改写又有另一种改写方法

select t1.c1,t1.c2,t1.c3 from t1 where exists(select 1 from(select gid from t2 where d1 ='C2'union all select gid from t3 where e1 ='C2')A where t1.gid = A.gid)and(nvl(t1.c1,'C4')='C4')and(t1.c2 liketrim('D4%')or t1.c3 liketrim('E7%'));

计划：

两种改写方法计划一致，性能都提升几十倍。
。

3、小结

or条件过滤性很好就比较适合做union all，这个例子也是对前面or关联案例的补充。