498
Oracle 12.2 的連接消除特性
編輯手記：在12.1及以前的版本中，當祖父，父，子表之間有明顯的主鍵和引用完整性約束，隻有加入的主鍵是單個列鍵時，才能進行連接消除; 但在12.2多列主鍵也允許發生連接消除，優化器從內聯視圖中刪除父對象，並在子對象和祖父對象之間留下連接。
很多人曾提出這樣的問題，在一條SQL語句當中，from 子句所包含的表的順序對SQL的執行計劃或者SQL的性能有沒有影響，從粗略的層麵來講，是沒有影響的，但有一些特殊的情況可能會產生不一樣的結果。
當考慮連接表的順序時，優化器有幾個內置的算法，用於選擇表的初始連接順序，並通過對初始順序對調整獲得最終的連接順序。假定其他因子相等時，具有最低優先級的標準之一是通過檢測from語句中的表的順序來決定，這樣如果在from子句中有足夠多的表，就會形成很多個連接表的子集，然後通過改變每個子集中的表的連接順序，決定最終的表的連接順序。（參考：https://jonathanlewis.wordpress.com/2006/11/03/table-order/）
上述的參考文章說以前寫的，距離現在已經11年了，那時候，還沒有人跟我談到由於SQL子句順序改變導致的連接表順序變化的其他情況。而現在，很多轉換都在優化器內實現，情況就會變得複雜。所以如果有一些特殊的情況是我沒有考慮到的，大家都可以通過留言或其他方式提出來，我再次測試驗證。
不久前，我在測試Oracle12.2點新特性的時候，無意中發現了一種改變表的連接順序的情況。
我當時使用的SQL語句如下：
正如你接下來將看到的三個表，祖父，父，子有明顯的主鍵和引用完整性約束。 這意味著祖父項具有單列主鍵，父項具有雙列主鍵，子項具有三列主鍵。 查詢沿著它們的主鍵連接三個表，然後僅從子表中選擇數據，因此它是連接消除的一個很好的例子。
在早期版本的Oracle連接中，隻有當加入的主鍵是單個列鍵時，才能進行消除，因此12.1和更早版本將隻能從此三表連接中消除祖父項; 但在12.2多列主鍵也允許發生連接消除，所以我們可能希望我們從這個查詢中獲得的計劃將消除祖父母表和父表。 執行計劃如下：
輸出結果跟預期有些偏差， 優化器已經設法消除表的父 - 所以看起來像“單列主鍵”連接消除已經工作，但“多列”連接消除沒有出現。
另一方麵，這條語句並沒有遵循通常寫SQL的規則。 如果遵循通常都書寫模式，子句將是順序child - > parent - > grandparent，按照我期望訪問它們的順序列出表。 改變之後我們再看執行計劃：
可見，基於多列主鍵的連接消除不起作用，可能必須按照列出from子句中的表的順序菜可以產生預期的結果。
注意：
如果想知道從Oracle語法切換到ANSI語法是否會有所不同，說明如下：使用ANSI語法，如果SQL按照 grandparent - > parent - > child的順序列出表，祖父項和父項都會被刪除 該順序對於Oracle語法不能正常工作），並且對於child- > parent - > grandparent，隻消除父代。 換句話說，兩種語法選項都有一個失敗點，但它們的失敗是反向的。
創建表
創建索引和約束
以下測試不同的連接順序：
情況一
情況二
情況三
情況四
當然，針對不同的係統統計信息，或I/O標準，擴展區大小，段空間管理或塊大小，以及各類參數，結果都可能會發生改變。
如果你想知道為什麼傳統方式和ANSI語法在進行連接時會選擇相反的處理方向，記住，ANSI SQL首先被轉換成一個等效的Oracle形式，在簡單的情況下，前兩個表形式第一個查詢塊然後每個表之後引入一個新的查詢塊，因此優化器策略（大約）進行以下翻譯：
然後優化器優化內聯查詢，消除祖父級在父級和子級之間留下聯接，最後才允許父級被刪除。
但我們得到的結果如下：
在這種形式下，優化器從內聯視圖中刪除父對象，並在子對象和祖父對象之間留下連接 - 因此無需進一步消除。
文章轉自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 10:32:52