SQL最佳化 之 -- join

2015.06.03
   一次面試中被問到關於 join方面的問題，當時沒有做回答，因為我對join的影響很模糊，因為沒做過開發，所以認識不是很深刻，就知道驅動表與被驅動表，返回的效果。剛剛在網上特地看了很多關於join的部落格，算是對join加深影響吧，尤其是自看SQL執行計劃的時候，剛開始不會區分各種型別的join，下面一篇是貼過來的，看著還行吧。
當sql訪問多個表時，關聯對sql效率就有很重要的影響。關聯要考慮兩個因素，join的型別和join的次序。
一、 JOIN的分類
(一) Nested loop join
1. 適用條件
1)關聯少量資料(rows)，返回集小
2)關聯條件能高效訪問第二張表(inner table)。高效訪問的關聯條件如'='，反之非高效的關聯條件如'!=','>'等;inner table（即非驅動表）上要有索引。
因此比較適合OLTP系統，因為OLTP系統中一般返回資料量小，而且表上面索引較多。[@more@]

2. 實現步驟
1) 最佳化器選擇驅動表(driving table)，指定其為outer table
2) 指定另一張表為inner table(非驅動表)
3) 根據outer table的每行記錄的關聯欄位，來訪問inner table。如下所示：
NESTED LOOPS 
outer_loop 
inner_loop
由於Nested loop從outer table向inner table查詢，關聯的次序就比較重要了。
3. nested loop join的例子
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM hr.employees e, hr.departments d
WHERE d.department_name IN ('Marketing', 'Sales')
AND e.department_id = d.department_id;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 722 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 220 | 1 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 19 | 722 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 2 | 32 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

(二) Hash join
1. 適用條件
1) 僅用於等值關聯equijoin（如=）;
2) 滿足下列任一條件：
大表關聯
或者小表的大部分記錄參與關聯
2. 實現機制
1) 最佳化器選擇較小的表，基於join key構建hash table。（驅動表）
2) 掃描另外一張較大的表，並在hash table中搜尋關聯行
如果記憶體足夠，小表全部在記憶體中，這種情況是最優的，成本可估算為兩張表各一次全表讀。
如果記憶體不夠，則小表的一部分可以放在temporary tablespace中（Temp表空間應足夠大），以儘可能提高io速度。

3. 例子
SELECT o.customer_id, l.unit_price * l.quantity
FROM orders o ,order_items l
WHERE l.order_id = o.order_id;

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)|
|* 1 | HASH JOIN | | 665 | 13300 | 8 (25)|
| 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)|
| 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)|
--------------------------------------------------------------------------

(三) Sort merge join
1. 適用情況
通常情況下hash join效能更好，但如果關聯的資料已經排序或不需排序，則sort merge join效能會更好
非等值關聯（nonequi join,如<,> ）時很有用，因為sort merge join在返回集很大時比nested loop效能好，而hash join又只能在equijoin中使用。
2. 實現機制
1) Sort操作：關聯資料按照關聯欄位進行排序。如果資料本來就是排序的，就不需此操作
2) Merge操作：經過排序的資料進行merge操作。
需要說明的是,sort merge join沒有driving table的概念
(四) 笛卡爾連線
無關聯條件，應儘可能避免。
(五) Outer join
是simple join的擴充套件，
SELECT cust_last_name, sum(nvl2(o.customer_id,0,1)) "Count"
FROM customers c, orders o
WHERE c.credit_limit > 1000
AND c.customer_id = o.customer_id(+)
C表稱為preserved table,o表稱為optional table
Outer join分為：
Left outer join
Right outer join
Full outer join
和普通join相比，outer join也可以是nested loop、hash join、sort merge等。但有一些不同之處：
1. Nested loop outer join中，以preserved table作為驅動表，而不是像普通join基於cost來選擇驅動表
2. Full outer join（equijoin）在11g中，自動使用基於hash join的演算法。執行計劃中出現HASH JOIN FULL OUTER。
可以用hint:NATIVE_FULL_OUTER_JOIN/NO_NATIVE_FULL_OUTER_JOIN來指定使用或不使用這一演算法。如果不使用，則full outer jion的執行計劃是left outer join和right outer jion的union。

二、 Join次序

基本原則是：記錄少的先關聯，這樣參與後續關聯的記錄數就會少。具體來說：
1)選擇能排除掉最多記錄的表作為driving table
2)剩餘的表中，選選擇有最好的filter的表（排除最多記錄）作為首先參與關聯的表
3)以此類推
看這個例子：
SELECT info
FROM taba a, tabb b, tabc c
WHERE a.acol BETWEEN 100 AND 200
AND b.bcol BETWEEN 10000 AND 20000
AND c.ccol BETWEEN 10000 AND 20000
AND a.key1 = b.key1
AND a.key2 = c.key2;假設a表經過filter後記錄最少，b次之，c記錄最多。那麼可以用a作為driving table,先與b關聯，最後與c關聯

三． 使用hint選擇關聯方式和次序
(一)使用hint指定關聯方式
Oracle最佳化器自動選擇join的方式，但有時不是最優的，開發人員可使用hint來選擇join方式，比較執行效率。相關的hint有：
USE_NL,USE_HASH,USE_MERGE
Exists子句中，HASH_SJ,MERGE_SJ,NL_SJ
Not in子句中，HASH_AJ,MERGE_AJ，NL_AJ

(二) 使用hint指定關聯次序
如果oracle最佳化器選擇的關聯次序不是你所希望的，可以用hint（leading和ordered）來指定。Ordered表示按照sql語句中表出現的先後次序，leading則可任意指定，更為通用。
Leading指定了driving table的選定次序。（在nested loop中,driving table就是outer table，在hash join中，是hash table。）
SELECT /*+ leading (a b c) */info
WHERE a.acol BETWEEN 100 AND 200
AND b.bcol BETWEEN 10000 AND 20000
AND c.ccol BETWEEN 10000 AND 20000
AND a.key1 = b.key1
AND a.key2 = c.key2;

(三) Undocumented hint引數:swap_join_inputs
注意，上面例子中，a作為驅動表和b關聯，關聯結果作為驅動表，再和c關聯。有時需要改變次序,如下面例子
SELECT /*+ leading (a b c)*/ info
WHERE a.key1 = b.key1
AND b.key2 = c.key2;
假如a 1000條，b 10萬條，c 1萬條。由於a和c表沒有關聯欄位，因此a和b先關聯，再和c關聯。但a關聯b產生2萬條記錄，和c關聯時，希望以c為驅動表，能否實現呢？
在hash_join中可以用oracle的隱含hint引數swap_join_inputs實現：
SELECT /*+ leading (a b c) swap_join_inputs(c) */ info
WHERE a.key1 = b.key1
AND b.key2 = c.key2;