一条"简单"的sql语句和小兔子买面包的故事

有时候开发人员写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已经没有那么明显的差别了。而且它会从数据的分布角度进行计划的解析。从第一个执行计划来看，数据库分析的还是很合理的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1298680/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1298680/