一個有趣的 SQL 解法：如何騙過 Oracle Optimizer

理論上，在 cost-based 的情況下，只要有定時的去跑統計資料，oracle optimizer 會自動幫你選擇最好的 executing plan，但有時候還是會例外的。

下面這個 sql 是前陣子同事的某 sql：（table 和 column 的名稱我處理過了，這個 sql 其實要計算某欄位的 sum，但不影響這個 sql 的討論，所以我把它拿掉，這樣 executing plan 看起來比較簡單一點）

table1 是個超過百萬筆資料的大 table，table2 只有幾千筆資料，sql 中總共有七個變數，但其中 :b2 到 :b6 這五個都和 table 本身的資料沒有任何關係。

上圖下方的 executing plan 結果的 cost 看起來還不錯，但 nested loop 的情況下，那個 cost 並不是那麼的準，光就已知的兩個 table 大小來看，先 full table scan 較小的 table2，再去和較大的 table1 join一定會有問題。

好吧，先把 :b2 到 :b6 都給值之後再來看一下展出來的 plan：

inner table 變成了 table 1，而且有用到它的 index。雖然看起來 cost 比較高，但實際上的執行效果是比較好的。

因為這段 sql 是在某個 procedure 中，需要反覆的使用，所有的變數組合超過二十組（也就是這個 sql 會執行超過二十次）。當然可以採用 dynamic sql 來解決這個問題，這樣就可以都用到正確的 plan，但是這樣就失去了 passing 的優勢了；或著是採用 hint 的方式來執行，但非必要的情況下，我並不想要採用 hint 去指定。

所以該怎麼在 bind variable 的情況下讓 oracle optimizer 展出第二張圖那樣的 executing plan 呢？

於是我把 sql 動了點手腳，如下圖：
在 sql 中加上了一些無用的條件，來騙過 oracle optimizer，讓它以為 table1 用到的條件也很多，於是 table1 就變成了 inner table，也達到了我要的目的。

同事的這個 sql 就這樣的被解決了。


備註：其實我覺得這個 table 一開始建立的時候就沒有規劃好，才會這樣儲存資料，以致有這樣的 sql 出現，但因為已經是歷史共業，只好將就的去解決這問題。 XD