一條"簡單"的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已經沒有那麼明顯的差別了。而且它會從資料的分佈角度進行計劃的解析。從第一個執行計劃來看，資料庫分析的還是很合理的。