有学员在开发过程遇到下面类似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改写来避免低效. 新版本除了大家熟知的新特性以为,还隐藏着很多类似的功能. 升级还是值得的!
索引专题培训继续接受报名,这次培训是围绕索引的一个体系的系统讲解,机会难得. 报名详情: 索引专题线上培训日期变更为5.16+5.23
