外連線與連線順序

在Oracle 9i中，外連線決定了連線的順序，比如TEST1.id=TEST2.id(+)，那麼表TEST1一定是驅動表（即從表TEST1去join表TEST2），不管採用nest loop join或者hash join。因為外連線中，表TEST1的所有記錄都要返回，所以必須用它來作為驅動表，如果反過來用表TEST2作為驅動表，實現比較困難，這個其實很好理解。

select a.* from TEST1 a,TEST2 b where a.id=b.id(+);

------------------------------------------
| Id  | Operation                | Name  |
------------------------------------------
|   0 | SELECT STATEMENT         |       |
|*  1 |  HASH JOIN OUTER         |       |
|   2 |   TABLE ACCESS FULL      | TEST1 |
|   3 |   TABLE ACCESS FULL      | TEST2 |
------------------------------------------

在10g版本中，Oracle引入了一個HASH JOIN RIGHT OUTER，可以改變外連線的順序。Oracle會根據COST來決定hash join的順序，比如Oracle發現表TEST2比TEST1小很多時，Oracle將會改變hash join的順序，因為根據hash join的原理，選擇比較小的表build hash table效率比較高。看看下面的執行計劃：

------------------------------------------
| Id  | Operation                | Name  |
------------------------------------------
|   0 | SELECT STATEMENT         |       |
|*  1 |  HASH JOIN RIGHT OUTER   |       |
|   2 |   TABLE ACCESS FULL      | TEST2 |
|   3 |   TABLE ACCESS FULL      | TEST1 |
------------------------------------------

因為是外連線，TEST1表的記錄需要全部返回，如果用TEST2表作為驅動表，Oracle一定做了特殊的處理。我們猜測一下Oracle如何處理，首先是採用普通的inner join的方法，將TEST2表在記憶體中build hash table（hash table A)，然後掃描TEST1表，並進行hash join，如果TEST1表的記錄未在hash table A中找到對應的記錄，則在另外的記憶體區域記錄(hash table B)，然後將兩個hash table合併，返回。這個特性僅適用於hash join，不適用於nest loop join，也沒有hash join left outer.

我們再來看看anti join（反連線）的情況：

select a.* from TEST1 a WHERE not exists(select null from TEST2 b where a.id=b.id);

可以等價轉換為外連線的形式

select a.* from TEST1 a,TEST2 b where a.id=b.id(+) and b.id is null;(只取TEST1表的內容)

------------------------------------------
| Id  | Operation                | Name  |
------------------------------------------
|   0 | SELECT STATEMENT         |       |
|*  1 |  HASH JOIN ANTI          |       |
|   2 |   TABLE ACCESS FULL      | TEST1 |
|   3 |   TABLE ACCESS FULL      | TEST2 |
------------------------------------------

select a.id,b.name from TEST1 a,TEST2 b where a.id=b.id(+) and b.id is null;(取兩個表的內容)

------------------------------------------
| Id  | Operation                | Name  |
------------------------------------------
|   0 | SELECT STATEMENT         |       |
|*  1 |  FILTER                  |       |
|*  2 |   HASH JOIN OUTER        |       |
|   3 |    TABLE ACCESS FULL     | TEST1 |
|   4 |    TABLE ACCESS FULL     | TEST2 |
------------------------------------------

第一個SQL使用了反連線，因為它只需要返回TEST1表並且不在TEST2表中的記錄，所以反連線在join的過程中，如果發現沒有匹配的記錄，只需要在TEST1表的hash table中作標記，然後返回即可。而第二個SQL需要返回兩個表的記錄，所以必須將TEST2表也build到hash table中，並且多了一個filter的工作（用來過濾b.id is null)，所以反連線就是一種特殊的外連線，而且連線的順序也是固定的，anti nest loop join的原理也是一樣的。

再讓我們看看HASH JOIN RIGHT OUTER用在反連線的環境中，我們又有新的發現，那就是HASH JOIN RIGHT ANTI.

select a.* from TEST1 a,TEST2 b where a.id=b.id(+) and b.id is null;(只取TEST1表中的內容)

------------------------------------------
| Id  | Operation                | Name  |
------------------------------------------
|   0 | SELECT STATEMENT         |       |
|*  1 |  HASH JOIN RIGHT ANTI    |       |
|   2 |   TABLE ACCESS FULL      | TEST2 |
|   3 |   TABLE ACCESS FULL      | TEST1 |
------------------------------------------

select a.id,b.name from TEST1 a,TEST2 b where a.id=b.id(+) and b.id is null;(取兩個表的內容)

------------------------------------------
| Id  | Operation                | Name  |
------------------------------------------
|   0 | SELECT STATEMENT         |       |
|*  1 |  FILTER                  |       |
|*  2 |   HASH JOIN RIGHT OUTER  |       |
|   3 |    TABLE ACCESS FULL     | TEST2 |
|   4 |    TABLE ACCESS FULL     | TEST1 |
------------------------------------------

可以看到，雖然join的順序發生了變化，但是如果我們只取inner table中的欄位，就可以利用到反連線的特性。小小執行計劃的改變，蘊含了更深層次的原理。