三種表連線方式 最佳化總結

4個角度剖析經典連線         NL                    hashjoin                     MERGE  sort JOIN
表的訪問次數           NL驅動表訪問多少次        兩表各自只訪問1次               與hashjoin一樣
                        被驅動表就訪問多少次      或0次


表的驅動順序與效能     與驅動表順序有關          類似NL，也與驅動順序有關         與驅動順序無關


表連線是否有排序       NL無排序                  hash join無排序，（消耗記憶體）    有排序產生   


各連線的使用限制        支援各種寫法              不支援><like<>等，限制最多     支援><但不支援like<>;


3中連線最佳化要點：
NL：1.請確保用在掃描的OLTP場景
    2.驅動表的限制條件要考慮建立索引
    3.被驅動表的連線條件要考慮建立索引
    4.確保小結果集先驅動，大的被驅動
Hash Join
    1.適用場景：請確保用在掃描的OLAP場景；明確該sql是否限制Hash join；兩表無任何索引傾向hash join；
    2.被驅動表的連線條件要有索引，被驅動表的限制條件是用不上索引；
    3.兩表的限制條件有索引（看返回量)
    4.保證小結果集先驅動，大的再驅動
    5.儘量保證pga能容納hash運算；
merge sort join
   適用場景：請確保用在全掃描的OLAP場景，sql是否限制Merge sort join
   1.兩表的限制條件有索引（看返回量）；
   2.連線條件索引消除排序（不完美）；
   3.避免取多餘列致排序的尺寸太大
   4.pga能完成排序，避免磁碟排序；
  
    








1、巢狀迴圈連結（USE_NL）
主要消耗的相關資源：CPU，磁碟I/O
特點：在網上看到很多關於這方面的帖子，說小表作為驅動表，其實這種說話不完全正確，其實帶條件的大表返回較少行的結果集也可以作為驅動表（外部表）。此表連線在當有高選擇性索引或進行限制性搜尋時候效率較高，可以快速返回第一次的搜尋結果。
缺點：當索引丟失或查詢條件限制不夠時，效率很低。
最佳化要點：




2、排序合併連線（USE_MERGE）
主要消耗的相關資源：記憶體，臨時表空間
特點：在排序合併連線中，ORACLE分別將第一個源表、第二個源表按他們各自要連線的列排序，然後將兩個已經排序的源表合併。由於沒有索引，排序合併連線在合併兩表錢將強制對他們進行排序，兩表都需要進行全表掃描，所以表名在FROM自居中出現的次序並不重要。當缺乏索引或搜尋條件模糊時，該型別的連線比巢狀迴圈連線更加有效。
缺點：所有表都需要排序，它為最最佳化的吞吐量而設計，並且在結果沒有全部找到之前不返回資料。
3、雜湊連線（USE_HASH）
主要消耗的相關資源：記憶體，臨時表空間
特點：在雜湊連線中，ORACLE訪問第一張表（通常是連線結果中較小的表），並且在記憶體中建立一張基於連線鍵的雜湊表，然後他掃描另外一張表（較大的表），並根據雜湊表檢測是否有匹配的記錄。當缺乏索引或搜尋條件模糊時，該型別的連線比巢狀迴圈連線更加有效。通常比排序合併連線更快。
缺點：為建立雜湊表，需要大量的記憶體，第一次結果返回慢，如果在磁碟上操作，速度將更慢。