75-当left join遇到子查询

有学员在开发过程遇到下面类似SQL,执行效率比较差,我对SQL做了简化处理,如下:

select count(*)

from t1

left join t2 on t1.object_id=t2.object_id

and t2.owner in (select username from t_users );

在12c之前,我希望得到3个表做hash join的执行计划,但是很遗憾,不行,优化器只能生成如下执行计划(如果left join变成inner join,就能得到我想要的执行计划; 把 in 换成 exists ,也是一样的情况):

怎么办? 很简单的改写:

select

count(*) from t1 left join

(select * from t2 where t2.owner in (select username from t_users )) v_t2

on t1.object_id=v_t2.object_id;

改写后,得到我需要的执行计划:

从以上两执行计划的比较可以看出,在当前不到10万的数据量情况下,改写后的sql执行效率提升了将近200倍(t1表越大,效率差别也越大).

这个事实说明,oracle的优化器,在11g的时候,还是有一些小缺憾. 好消息就是,12c版本把这种情况考虑进去了,不需要改写,也能生成我想要的执行计划:

select count(*)

from t1

left join t2 on t1.object_id=t2.object_id

and t2.owner in (select username from t_users );

这个功能是由隐含参数_optimizer_ansi_join_lateral_enhance控制,如果把这个参数设置为false,就又回到11g的低效执行计划了.

对应的hint是DECORRELATE (反向操作就是NO_DECORRELATE),不改隐含参数,使用NO_DECORRELATE,也能回到11g的执行计划.大家有兴趣可以在12c版本上试试.

总结:

优化器是数据库性能的核心,SQL写法复杂多变,我们希望优化器能够更加聪明一些,但是毕竟还是基于规则而不是人工智能. 对于优化器规则有限制的情况,一般我们都能通过SQL改写来避免低效. 新版本除了大家熟知的新特性以为,还隐藏着很多类似的功能. 升级还是值得的!