有时候开发人员写sql语句的时候,接触的性能问题越多,可能对sql语句的结构，性能考虑会多一些，这也是一件好事，不过如果考虑不当，本来原本想做的的一些优化却使得问题变得更加严重。
在生产环境中我们对指定的客户端都有一定的监控，在下午的时候发现一个sql语句执行的时间太长了，抓到语句，一看倒不复杂，是一个开发人员运行的。
从他的sql语句可以看出他在尝试自连接account表（account表示百万级别的）,l9_id不是account的主键，ban这个字段是主键字段。
select *
from account a
where l9_id = 'XX'
and l9_id is not null
and exists (select 1
from account
where l9_id = a.l9_id
and ban a.ban
and rownum = 1)
order by a.l9_id
他要做的查询从结构上来看类似下面的形式，比如表account里的数据如下
ban l9_id
1 1001
2 1001
3 1002
4 1001
5 1002
每个account对应一个l9_id字段，是不唯一的，比如根据account ban=1得到l9_id=1001,现在要查的是除了ban=1之外的l9_id为1001的ban列表。
从sql结构中可以看到，开发人员还专门使用了exists rownum，看起来好像还不错。
来看看oracle生成的执行计划。
你会看到cost已经到1886M，时间是999:59:59,从计划来看,oracle都不知道什么时候能执行完成。
的确从生成环境来看，这条语句执行很慢，用了2个小时。
SQL> @plan
Plan hash value: 2418382151
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 204 | | 1886M (1)|999:59:59 |
| 1 | SORT ORDER BY | | 1 | 204 | 537M| 1886M (1)|999:59:59 |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS FULL | ACCOUNT | 1651K| 321M| | 33945 (1)| 00:06:48 |
|* 4 | COUNT STOPKEY | | | | | | |
|* 5 | TABLE ACCESS FULL| ACCOUNT | 2 | 26 | | 1146 (1)| 00:00:14 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "ACCOUNT" "ACCOUNT" WHERE
ROWNUM=1 AND "L9_ID"=:B1 AND "BAN":B2))
3 - filter("L9_COMPANY_CODE"='XX' AND "L9_ID" IS NOT NULL)
4 - filter(ROWNUM=1)
5 - filter("L9_ID"=:B1 AND "BAN":B2)
可以举个笑话来说明一下，这个逻辑的问题，
有一天，小白兔到面包房买面包，问老板，“老板啊，你有100个面包吗？",老板说没有啊，小白兔第二天又去买面包，又问老板有没有100个面包啊。老板还是说没有，第三天的时候老板忙了很久终于做好了100个面包，小白兔又来买面包了，问老板你又100个面包吗，老板高兴的说有啊，小白兔也高兴的说，给我买一个！
如果仔细分析逻辑，就会发现那个rownum是画蛇添足，本来可以顺利得到ban的列表，但是反复循环，循环几百万次，每次都是一个全表扫描，还不一定能够查到对应的ban值。
对于这个语句，其实不用那么复杂。写成下面的形式就好。基本没有什么特别的地方。
select t1.ban from account t1,account t2
where t1.id = 'TD'
and t1.id is not null and t2.l9_id is not null
and t1.l9_id=t2.l9_lid
and t1.ban!=t2.ban
执行计划来看确实是一个可以达到目标的计划。
Plan hash value: 1286362100
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48M| 1353M| | 78038 (2)| 00:15:37 |
|* 1 | HASH JOIN | | 48M| 1353M| 44M| 78038 (2)| 00:15:37 |
|* 2 | TABLE ACCESS FULL| ACCOUNT | 1651K| 25M| | 33924 (1)| 00:06:48 |
|* 3 | TABLE ACCESS FULL| ACCOUNT | 6605K| 81M| | 33915 (1)| 00:06:47 |
------------------------------------------------------------------------------------------
所以,有些东西还是大道至简的道理，其实oracle内部也做了很多的优化，对于exists和in已经没有那么明显的差别了。而且它会从数据的分布角度进行计划的解析。从第一个执行计划来看，数据库分析的还是很合理的。
在生产环境中我们对指定的客户端都有一定的监控，在下午的时候发现一个sql语句执行的时间太长了，抓到语句，一看倒不复杂，是一个开发人员运行的。
从他的sql语句可以看出他在尝试自连接account表（account表示百万级别的）,l9_id不是account的主键，ban这个字段是主键字段。
select *
from account a
where l9_id = 'XX'
and l9_id is not null
and exists (select 1
from account
where l9_id = a.l9_id
and ban a.ban
and rownum = 1)
order by a.l9_id
他要做的查询从结构上来看类似下面的形式，比如表account里的数据如下
ban l9_id
1 1001
2 1001
3 1002
4 1001
5 1002
每个account对应一个l9_id字段，是不唯一的，比如根据account ban=1得到l9_id=1001,现在要查的是除了ban=1之外的l9_id为1001的ban列表。
从sql结构中可以看到，开发人员还专门使用了exists rownum，看起来好像还不错。
来看看oracle生成的执行计划。
你会看到cost已经到1886M，时间是999:59:59,从计划来看,oracle都不知道什么时候能执行完成。
的确从生成环境来看，这条语句执行很慢，用了2个小时。
SQL> @plan
Plan hash value: 2418382151
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 204 | | 1886M (1)|999:59:59 |
| 1 | SORT ORDER BY | | 1 | 204 | 537M| 1886M (1)|999:59:59 |
|* 2 | FILTER | | | | | | |
|* 3 | TABLE ACCESS FULL | ACCOUNT | 1651K| 321M| | 33945 (1)| 00:06:48 |
|* 4 | COUNT STOPKEY | | | | | | |
|* 5 | TABLE ACCESS FULL| ACCOUNT | 2 | 26 | | 1146 (1)| 00:00:14 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "ACCOUNT" "ACCOUNT" WHERE
ROWNUM=1 AND "L9_ID"=:B1 AND "BAN":B2))
3 - filter("L9_COMPANY_CODE"='XX' AND "L9_ID" IS NOT NULL)
4 - filter(ROWNUM=1)
5 - filter("L9_ID"=:B1 AND "BAN":B2)
可以举个笑话来说明一下，这个逻辑的问题，
有一天，小白兔到面包房买面包，问老板，“老板啊，你有100个面包吗？",老板说没有啊，小白兔第二天又去买面包，又问老板有没有100个面包啊。老板还是说没有，第三天的时候老板忙了很久终于做好了100个面包，小白兔又来买面包了，问老板你又100个面包吗，老板高兴的说有啊，小白兔也高兴的说，给我买一个！
如果仔细分析逻辑，就会发现那个rownum是画蛇添足，本来可以顺利得到ban的列表，但是反复循环，循环几百万次，每次都是一个全表扫描，还不一定能够查到对应的ban值。
对于这个语句，其实不用那么复杂。写成下面的形式就好。基本没有什么特别的地方。
select t1.ban from account t1,account t2
where t1.id = 'TD'
and t1.id is not null and t2.l9_id is not null
and t1.l9_id=t2.l9_lid
and t1.ban!=t2.ban
执行计划来看确实是一个可以达到目标的计划。
Plan hash value: 1286362100
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48M| 1353M| | 78038 (2)| 00:15:37 |
|* 1 | HASH JOIN | | 48M| 1353M| 44M| 78038 (2)| 00:15:37 |
|* 2 | TABLE ACCESS FULL| ACCOUNT | 1651K| 25M| | 33924 (1)| 00:06:48 |
|* 3 | TABLE ACCESS FULL| ACCOUNT | 6605K| 81M| | 33915 (1)| 00:06:47 |
------------------------------------------------------------------------------------------
所以,有些东西还是大道至简的道理，其实oracle内部也做了很多的优化，对于exists和in已经没有那么明显的差别了。而且它会从数据的分布角度进行计划的解析。从第一个执行计划来看，数据库分析的还是很合理的。
