首页 > Oracle > Oracle in 和 exists执行效率,到底谁高?
2014
12-17

Oracle in 和 exists执行效率,到底谁高?

在测试之前,我们先来了解下执行计划中的filter,以前也说过,这里先温习下filter。不过如果有面试问到这个问题:到底in效率高还是exists效率高?这个问题希望你可以在这里找到答案,先来看filter吧。

1. 创建2个测试表,并给test2创建索引

2. 查看执行计划

在这个执行计划中,test1作为主表,可以在operation中看到执行计划中有filter,以前已经说过,filter的算法就好比NL,test1表有86970条记录,在谓词过滤信息中,可以看到B1这个绑定变量,这里通过test1中的object_id传给test2,因为test1表中的object_id是主键,也就是说id=3和id=4这2步会执行86970次

如果object_id有重复值,比如只有5W条,那id=3和id=4就只会执行5W次,filter传过的值就不会在继续传递。这里就和NL不一样,如果是NL的话,那id=3和id=4还是会执行86970次,驱动表返回多少条记录,被驱动表就要执行多少次。此外要注意的是,filter的驱动表是固定的,我这个sql当中,只能是from后面的表,不能是子查询中的test2表。而且大多数情况下,如果执行计划中出现filter,就不能改成hash,当然也有特殊情况可以改变执行计划,如果发现不能修改掉filter,而当前filter导致执行计划出现错误,可以从修改sql方面入手。

这里我们还需要 引入一个概念,subquery unnest子查询非嵌套,比如下面个sql

从执行计划中看出,这个sql解套了,operation中没有出现filter关键字,下面使用hint让sql不解套

上面说filter的时候,说过当执行计划中出现filter的时候,执行计划就固定死了。emp表作为驱动表。当然还有一种情况,在执行计划中出现filter只有一个儿子,这种filter下,只有一个操作,这里的filter就只相当于过滤,并不是相当于嵌套循环。

这个operation中的filter就只起到过滤作用,此外在id=3这一步, 可以看到VIEW | VW_NSO_1关键字,http://www.savedba.com/?p=824 这篇文章对这种自动生成的视图有简单的介绍,说明子查询被固化了,之所以会被固化,是因为在子查询中添加了rownum过滤条件关键字,下面看一下修改成exists版本的执行计划

可以看到改成exists之后,全是filter了,虽然此处2个sql实际可能不一样,但如果in改为exists之后,子查询中就有2个表的数据,rownum就起不到固化子查询的作用,所以在使用exists的时候,如果子查询中包含了会固化子查询的关键字(比如rownum,start with,union,cube等),这里就会出现filter,但如果是in的话,即使子查询会被固化,这里也不会有影响,因为子查询当中并没有主表的过滤,所以不会出现filter。当然这里的说法不包括所有情况。

现在是不是可以回答面试官问到的那个问题了,到底是in好还是exists好?按我来说的话,还是没有with as好。O(∩_∩)O~

最后编辑:
作者:Jerry
一个积极向上的小青年,热衷于分享--Focus on DB,BI,ETL