在Oracle資料庫中，兩個表之間的表連接方法有排序合併連接、嵌套迴圈連接、哈希連接和笛卡爾連接四種 1.排序合併連接（sort merge join） 排序合併連接是一種兩表在做表連接時用排序（SORT）操作和合併（MERGE）操作來得到連接結果集的表連接方法 如果t1表和t2表在做表連接時使用的 ...
在Oracle資料庫中，兩個表之間的表連接方法有排序合併連接、嵌套迴圈連接、哈希連接和笛卡爾連接四種
1.排序合併連接（sort merge join）
排序合併連接是一種兩表在做表連接時用排序（SORT）操作和合併（MERGE）操作來得到連接結果集的表連接方法
如果t1表和t2表在做表連接時使用的是排序合併連接，那麼Oracle會依次執行如下步驟：
a.以目標SQL中指定的謂詞條件訪問t1表，然後對訪問結果按照t1表的連接列排序，排好序後的結果集記為s1
b.以目標SQL中指定的謂詞條件訪問t2表，然後對訪問結果按照t2表的連接列排序，排好序後的結果集記為s2
c.對s1和s2進行合併操作，從中取出匹配記錄作為最終的結果集
排序合併連接的優缺點及適用場景：
a.通常情況下hash join的效果都比sort merge join要好，但是，如果行源已經被排過序，在執行sort merge join時不需要再排序，這時sort merge join的性能會優於hash join
b.通常情況下，只有在以下情況發生時，才會使用排序合併連接：
1)RBO模式
2)不等值連接（>,<,>=,<=）
3)哈希連接被禁用時（_HASH_JOIN_ENABLED=false）
4)數據源已排序
--示例
1 SQL> select * from scott.emp t1,scott.emp t2 where t1.empno > t2.mgr; 2 3 89 rows selected. 4 5 6 Execution Plan 7 ---------------------------------------------------------- 8 Plan hash value: 3950110903 9 10 ---------------------------------------------------------------------------------------- 11 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 12 ---------------------------------------------------------------------------------------- 13 | 0 | SELECT STATEMENT | | 62 | 4712 | 6 (17)| 00:00:01 | 14 | 1 | MERGE JOIN | | 62 | 4712 | 6 (17)| 00:00:01 | 15 | 2 | SORT JOIN | | 14 | 532 | 2 (0)| 00:00:01 | 16 | 3 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 | 17 | 4 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 | 18 |* 5 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | 19 | 6 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | 20 ---------------------------------------------------------------------------------------- 21 22 Predicate Information (identified by operation id): 23 --------------------------------------------------- 24 25 5 - access(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR")) 26 filter(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR")) 27 28 29 Statistics 30 ---------------------------------------------------------- 31 1 recursive calls 32 0 db block gets 33 8 consistent gets 34 0 physical reads 35 0 redo size 36 6612 bytes sent via SQL*Net to client 37 575 bytes received via SQL*Net from client 38 7 SQL*Net roundtrips to/from client 39 2 sorts (memory) 40 0 sorts (disk) 41 89 rows processed 42 43 SQL>View Code
2.嵌套迴圈連接（nested loops join）
嵌套迴圈連接是一種兩表在做表連接時依靠兩層嵌套迴圈（外層迴圈/內層迴圈）來得到連接結果集的表連接方法
如果t1表和t2表在做表連接時使用的是嵌套迴圈連接，那麼Oracle會依次執行如下步驟：
a.首先，優化器會按照一定的規則來決定t1和t2誰是驅動表誰是被驅動表，驅動表用於外層迴圈，被驅動表用於記憶體迴圈。假設t1是驅動表
b.以目標SQL中指定的謂詞條件訪問驅動表t1，得到結果集s1
c.遍歷s1，同時遍歷被驅動表t2，即取出s1中的記錄按照連接條件和被驅動表t2做匹配。最終將得到的結果集返回
嵌套迴圈連接的優缺點及適用場景：
a.能夠實現快速響應，即可以第一時間先返回已經連接過且滿足連接條件的記錄，而不必等待所有的連接操作全部做完後才返回連接結果
b.適用於驅動表所對應的驅動結果集的記錄數較少，同時在被驅動表的連接列上又存在唯一性索引（或者在被驅動表的連接列上存在選擇性很好的非唯一性索引）的情況
--示例
SQL> select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno; SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID dcsf9m1rzzga5, child number 0 ------------------------------------- select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno Plan hash value: 4192419542 ------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 32 | | 1 | NESTED LOOPS | | 1 | 14 | 14 |00:00:00.01 | 32 | | 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 7 | |* 3 | TABLE ACCESS FULL| EMP | 4 | 4 | 14 |00:00:00.01 | 25 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T1"."DEPTNO"="T2"."DEPTNO") 21 rows selected. SQL>View Code
3.哈希連接（hash join）
哈希連接是一種兩表在做表連接時依靠哈希運算來得到連接結果集的表連接方法，oracle 7.3之後引入
Hash join的工作方式是將一個表（通常是小一點的那個表）做hash運算並存儲到hash列表中，從另一個表中抽取記錄，做hash運算，到hash 列表中找到相應的值，做匹配
哈希連接只適用於CBO，也只能用於等值連接條件
哈希連接很適合於小表和大表做連接，特別是在小表的連接列的可選擇性非常好的情況下，這時候哈希連接的執行時間就可以近似看作是和全表掃描那個大表所耗費的時間相當
哈希連接時，驅動結果集對應的Hash Table能夠完全被容納在記憶體中（PGA的工作區），此時的哈希連接的執行效率非常高
哈希連接的性能問題可以通過10104事件來診斷，相關說明如下：
Number of in-memory partitions (may have changed): Hash Partition
Final number of hash buckets: Hash Bucket數量
Total buckets: Empty buckets: Non-empty buckets: Hash Bucket中空記錄及非空記錄的情況
Total number of rows: 驅動結果集的記錄數
Maximum number of rows in a bucket: 包含記錄數最多的Hash Bucket所含記錄的數量
Disabled bitmap filtering: 是否啟用點陣圖過濾
--示例
1 SQL> select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno; 2 SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last')); 3 4 PLAN_TABLE_OUTPUT 5 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 6 SQL_ID 0j83q86ara5u2, child number 0 7 ------------------------------------- 8 select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp 9 t1,scott.dept t2 where t1.deptno = t2.deptno 10 11 Plan hash value: 615168685 12 13 ---------------------------------------------------------------------------------------------------------------- 14 | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | 15 ---------------------------------------------------------------------------------------------------------------- 16 | 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 13 | | | | 17 |* 1 | HASH JOIN | | 1 | 14 | 14 |00:00:00.01 | 13 | 1321K| 1321K| 1070K (0)| 18 | 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 6 | | | | 19 | 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 7 | | | | 20 ---------------------------------------------------------------------------------------------------------------- 21 22 Predicate Information (identified by operation id): 23 --------------------------------------------------- 24 25 1 - access("T1"."DEPTNO"="T2"."DEPTNO") 26 27 28 21 rows selected. 29 30 SQL>View Code
