Oracle中表的連接類型
?
??? Oracle中所謂表的連接，有兩種含義。一種是根據Oracle內部的連接判斷方式區分的內部連接類型，這會影響到系統優化、資源占用等問題。另一種是基于查詢的方式，對兩張表之間建立聯系，會影響到查詢的結果，這里都寫一下。
?
?
一、Oracle
中的表內部連接有
3類
：
?
1、
嵌套循環連接(
Nested Loops
)
?
??? 原理：掃描一個表，每讀取驅動表的一條記錄，就根據索引去另一個表里面查找，所有匹配記錄放在結果集中，然后再讀取驅動表的下一行。沒有索引一般就不會是
nested loops
。
??? 條件：驅動表結果集不大，被驅動表連接字段要有索引。
??? 特點：使用嵌套循環連接是從連接結果中提取第一批記錄的最快速方法。
??? 使用：
USE_NL(t1 t2)
提示來強制執行
Nested Loops
。
?
2、
哈希連接(
Hash Join
)
?
??? 原理：優化器先掃描小表，根據連接鍵在內存中建立
hash
表，然后掃描大表，每得到一條記錄就探測
hash
表一次，找出匹配行。
??? 條件：兩個巨大表之間的連接，或一個巨大的表一個小表之間的連接。且連接鍵無索引。
??? 特點：需要較大的內存，如表太大則需要進行分區，并暫時存儲至磁盤的臨時段。掃描成本
=
全表掃描大表
+
分區數
*
表全表掃描小表；還需要注意的是：必須將
HASH_JOIN_ENABLED
設為
True,
并且為參數
PGA_AGGREGATE_TARGET
設置了一個足夠大的值后，才可以執行
Hash Join
。
??? 使用：
USE_HASH(t1 t2)
提示來強制執行
Hash Join
?
3、
排序合并連接(
Sort Merge Join
)
?
??? 原理：將兩個表分別進行排序，然后將兩個表合并，查找出匹配的記錄。
??? 條件：行源已經被排過序的情況下使用。
??? 特點：主要花費在兩個表的全表掃描和各自的排序上。
??? 使用：
USE_MERGE(t1 t2)
提示來強制執行
Sort Merge Join
。
?
?
??? 總結
：當缺少有用的索引時，哈希連接比嵌套循環連接更加有效。哈希連接可能比排序合并連接更快，因為在這種情況下只有一張源表需要排序。哈希連接也可能比嵌套循環連接更快，因為處理內存中的哈希表比檢索
B_Tree
索引更加迅速。
?
?
?
二、Oracle外部連接方式也分3類：
?
??? SQL> select * from t11;
??? ??????? C1???????? C2
??? ---------- ----------
??? ???????? 5????????? 5
??? ???????? 1????????? 1
??? ???????? 2????????? 2
??? SQL> select * from t22;
??? ??????? D1???????? D2
??? ---------- ----------
??? ???????? 1????????? 1
??? ???????? 2????????? 2
??? ???????? 3????????? 3
??? ???????? 4????????? 4
?
1、內連接（inner join）
?
??? SQL> select c1,c2,d1,d2 from t11 inner join t22 on t11.c1=t22.d1;
?
??? 等效于：(可簡寫為join)
??? SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1=t22.d1;
?
???
效果：
??????????? C1???????? C2???????? D1???????? D2
??? ---------- ---------- ---------- ----------
???????????? 1????????? 1????????? 1????????? 1
???????????? 2????????? 2????????? 2????????? 2
?
2、外連接（outer join）
?
?
①左外連接
?
??? SQL> select c1,c2,d1,d2 from t11 left outer join t22 on t11.c1=t22.d1;
?
??? 等效于：(可簡寫為left?join)
??? SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1=t22.d1(+);
?
???
效果：
??????????? C1???????? C2???????? D1???????? D2
??? ---------- ---------- ---------- ----------
???????????? 5????????? 5
???????????? 1????????? 1????????? 1????????? 1
???????????? 2????????? 2????????? 2????????? 2
?
?
②右外連接
?
??? SQL> select c1,c2,d1,d2 from t11 right outer join t22 on t11.c1=t22.d1;
?
??? 等效于：(可簡寫為right?join)
??? SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1(+)=t22.d1;
?
???
效果：
??????????? C1???????? C2???????? D1???????? D2
??? ---------- ---------- ---------- ----------
???????????? 1????????? 1????????? 1????????? 1
???????????? 2????????? 2????????? 2????????? 2
?????????????????????????????????? 4????????? 4
?????????????????????????????????? 3????????? 3
?
③全外連接
?
??? SQL> select c1,c2,d1,d2 from t11 full outer join t22 on t11.c1=t22.d1;
?
???
效果：
??? ??????? C1???????? C2???????? D1???????? D2
??? ---------- ---------- ---------- ----------
??? ???????? 5????????? 5
??? ???????? 1????????? 1????????? 1????????? 1
??? ???????? 2????????? 2????????? 2????????? 2
??? ?????????????????????????????? 4????????? 4
??? ?????????????????????????????? 3????????? 3
?
??? 等效于：(可簡寫為full?join)
??? SQL> select c1,c2,d1,d2 from t11,t22 where t11.c1=t22.d1(+)
??? ? 2? union
??? ? 3? select c1,c2,d1,d2 from t11,t22 where t11.c1(+)=t22.d1;
?
3、交叉連接（cross join）
?
??? SQL> select c1,c2,d1,d2 from t11 cross join t22;
?
??? 等效于：(其他join必須有on子句)
??? SQL> select c1,c2,d1,d2 from t11,t22;
?
???
效果：
??????????? C1???????? C2???????? D1???????? D2
??? ---------- ---------- ---------- ----------
???????????? 5????????? 5????????? 1????????? 1
???????????? 5????????? 5????????? 2????????? 2
???????????? 5????????? 5????????? 3????????? 3
???????????? 5????????? 5????????? 4????????? 4
???????????? 1????????? 1????????? 1????????? 1
???????????? 1????????? 1????????? 2????????? 2
???????????? 1????????? 1????????? 3????????? 3
???????????? 1????????? 1????????? 4????????? 4
???????????? 2????????? 2????????? 1????????? 1
???????????? 2????????? 2????????? 2????????? 2
???????????? 2????????? 2????????? 3????????? 3
???????????? 2????????? 2????????? 4????????? 4
??? 12 rows selected.
?
?
?