2014-09-25 BaoXinjian
一、摘要
Oracle三種主要連接方式的比較
1. Hash Join
(1).概述
i. 讀取一個表的資料，並將放置到內存中，並建立唯一關鍵字的位圖索引
ii. 讀取另一個表，和內存中表通過Hash算法進行比較
(2).適用對象
i. 大表連接小表
ii. 兩個大表
2. Nested Loops
(1).概述
i. 循環外表記錄
ii. 進行逐個比對和內標的連接是否符合條件
(2).適用對象
小表驅動大表，返回較少的結果集
3. Merge Join
(1).概述
i. 兩個表進行table access full
ii. 對table access full的結果進行排序
iii. 進行merge join對排序結構進行合並
(2).適用對象
通過rowid訪問數據
當sql訪問多個表時，關聯對sql效率就有很重要的影響。關聯要考慮兩個因素，join的類型和join的次序。
二、Join分類
1. Nested Loop Join方式
1.1 適用條件
(1). 關聯少量數據(rows)，返回集小。
(2). 關聯條件能高效訪問第二張表(inner table)。高效訪問的關聯條件如'='，反之非高效的關聯條件如'!=','>'等;inner table（即非驅動表）上要有索引。
因此比較適合OLTP系統，因為OLTP系統中一般返回數據量小，而且表上面索引較多。
1.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查詢，關聯的次序就比較重要了。
1.3 Nexted Loop Join的例子
2. Hash Join方式
2.1 適用條件
(1). 僅用於等值關聯equijoin（如=）;
(2). 滿足下列任一條件：
大表關聯
或者小表的大部分記錄參與關聯
2.2 實現機制
(1). 優化器選擇較小的表，基於join key構建hash table。（驅動表）
(2). 掃描另外一張較大的表，並在hash table中搜尋關聯行
如果內存足夠，小表全部在內存中，這種情況是最優的，成本可估算為兩張表各一次全表讀。
如果內存不夠，則小表的一部分可以放在temporary tablespace中（Temp表空間應足夠大），以盡可能提高io速度。
2.3 Hash Join的例子
3. Sort merge join
3.1 適用情況
通常情況下hash join性能更好，但如果關聯的數據已經排序或不需排序，則sort merge join性能會更好。
非等值關聯（nonequi join,如<,> ）時很有用，因為sort merge join在返回集很大時比nested loop性能好，而hash join又只能在equijoin中使用。
3.2 實現機制
(1). Sort操作：關聯數據按照關聯字段進行排序。如果數據本來就是排序的，就不需此操作
(2). Merge操作：經過排序的數據進行merge操作。
需要說明的是，sort merge join沒有driving table的概念
4. 笛卡爾連接
無關聯條件，應盡可能避免。
5. Outer Join
5.1 Simple Join的擴展
customers表稱為preserved table,orders表稱為optional table
5.2 Outer Join的擴展
5.3 和普通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選擇關聯方式和次序
1. 使用hint指定關聯方式
Oracle優化器自動選擇join的方式，但有時不是最優的，開發人員可使用hint來選擇join方式，比較執行效率。
相關的hint有：
2. 使用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;
3. 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;
********************作者：鮑新建*******************
參考：http://blog.itpub.net/18474/viewspace-1060728/
參考：metalink：How to switch the driving table in a hash join [ID 171940.1]
一樣的吧
如果用where是老語法的
用join是新語法
性能都一樣 數據庫優化器會自動識別
即使where後面加了很多其它條件
建議使用join on
因為寫where如果忘記寫關聯條件 就成笛卡爾積了
join on 是個好的習慣能避免這種不必要的錯誤出現
用下面的方法，同樣適用於32位的系統環境：
64位windows下，使用PL/SQL Developer連接Oracle：
•1. 下載32位Oracle InstantClient，並展開到某目錄，例如C:\instantclient-basic-nt-11.2.0.2.0；
•2. 將系統的tnsnames.ora拷貝到該目錄下；
•3. 在PLSQL Developer中設置Oracle_Home和OCI Library：
ToolsPreferencesOracleConnection：
Oracle_Home：C:\instantclient-basic-nt-11.2.0.2.0
OCI Library：C:\instantclient-basic-nt-11.2.0.2.0\oci.dll
•4. 在PLSQL Developer目錄下新建如下bat文件，替換其快捷方式，啟動PLSQL Developer：
@echo off
set path=C:\instantclient-basic-nt-11.2.0.2.0
set ORACLE_HOME=C:\instantclient-basic-nt-11.2.0.2.0
set TNS_ADMIN=C:\instantclient-basic-nt-11.2.0.2.0
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
start plsqldev.exe
