本文討論一下join技術(shù)背后的機(jī)制。我們知道常用的表連接有如下幾種
讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：國(guó)際域名空間、虛擬主機(jī)、營(yíng)銷軟件、網(wǎng)站建設(shè)、鎮(zhèn)安網(wǎng)站維護(hù)、網(wǎng)站推廣。
笛卡爾連接
內(nèi)連接
左外連接
右外連接
全連接
這些sql的寫(xiě)法，想必大家都很清楚了，那么這些連接的數(shù)據(jù)訪問(wèn)是如何實(shí)現(xiàn)的呢？
nested loop
我們看如下查詢
重慶分公司
重慶分公司
SQL> alter session set optimizer_mode=rule;
Session altered.
SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3625962092
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | EMP |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")根據(jù)我們之前講的執(zhí)行計(jì)劃解讀，本查詢是這樣實(shí)現(xiàn)的：
全表掃描emp表（非阻塞掃描，并不是將數(shù)據(jù)全部取出，才執(zhí)行下一步）。
將emp中的數(shù)據(jù)逐條取出，通過(guò)索引PK_DEPT查詢出索引中的rowid，結(jié)果集變成（ename，rowid）
將2生成的結(jié)果集逐條取出，通過(guò)rowid去訪問(wèn)dept表，結(jié)果集變成（ename，dname）
將結(jié)果集返回。
這種以循環(huán)的方式取出數(shù)據(jù)的join實(shí)現(xiàn)方式就叫嵌套循環(huán)。
此計(jì)劃可以用如下邏輯偽代碼實(shí)現(xiàn)
for y in （for x in （select * from emp）loop index lookup the rowid for x.deptno output joined record（ename，dept.rowid） end loop）loop select * from dept where rowid=y.rowid output joined record(ename,dname) end loop
我們把emp表稱之為驅(qū)動(dòng)表（注驅(qū)動(dòng)表與from子句的表順序無(wú)關(guān)，主要看執(zhí)行計(jì)劃）。
此種連接方式，適用于驅(qū)動(dòng)表返回?cái)?shù)據(jù)比較少，并且被驅(qū)動(dòng)表dept上deptno列有索引。如果查詢返回n行，那么dept表將被掃描n次。此連接擅長(zhǎng)于從結(jié)果集中迅速取出第一行。
Hash Join
Hash Join適合處理大型結(jié)果集，優(yōu)化器選擇兩個(gè)表或者源數(shù)據(jù)中比較小的，使用join key在內(nèi)存中建立一個(gè)hash table。然后掃描大表，并探查hash表，去發(fā)現(xiàn)匹配的記錄。
小表稱為驅(qū)動(dòng)表，大表稱為探查表
當(dāng)hash table能全部放到內(nèi)存中，此種情況最好。如果內(nèi)存中放不下hash table，優(yōu)化器將hash table分區(qū)，超出內(nèi)存范圍的分區(qū)將被寫(xiě)到臨時(shí)表空間中。
我們分兩種情況討論hash join的實(shí)現(xiàn)
hash table 全部在內(nèi)存里
hash table是Oracle根據(jù)join key利用一個(gè)hash函數(shù)將小表分割成多個(gè)bucket。hash table建立完成后，Oracle去掃描大表，并且采用相同的hash算法，將讀入的數(shù)據(jù)也分割成多個(gè)bucket。bucket與bucket之間進(jìn)行join運(yùn)算，返回結(jié)果。直到大表讀完為止。
2. hash table 不能全部放到內(nèi)存中
這種情況有點(diǎn)麻煩，當(dāng)Oracle發(fā)現(xiàn)內(nèi)存無(wú)法完全存放小表，Oracle在構(gòu)造hash table時(shí)，將小表進(jìn)行分區(qū)，每個(gè)分區(qū)中再構(gòu)造bucket 。當(dāng)內(nèi)存寫(xiě)滿時(shí)，Oracle將內(nèi)存中最大的分區(qū)寫(xiě)到tempfile中。用這個(gè)方法，直到小表hash table構(gòu)造完成。此時(shí)，hashtable一部分?jǐn)?shù)據(jù)在內(nèi)存，一部分?jǐn)?shù)據(jù)在tempfile。
當(dāng)Oracle去掃描大表時(shí)，如果掃描的行通過(guò)hash在內(nèi)存中能找到結(jié)果，就匹配成功。如果不能命中，則采用與hash table相同的分區(qū)方式，先將數(shù)據(jù)寫(xiě)入tempfile中。當(dāng)大表全部掃描完畢，hash table內(nèi)存中的部分已全部匹配完。此時(shí)依次將tempfile中的分區(qū)加載到內(nèi)存中。重新掃描大表臨時(shí)存在tempfile中的相應(yīng)分區(qū)加以匹配。直到數(shù)據(jù)全部處理完。
SQL> insert into big_emp select * from big_emp;
SQL> insert into big_emp select * from big_emp;#重復(fù)執(zhí)行多次
SQL> /
458752 rows created.
SQL> create table dept_new as select * from dept;
Table created.
SQL> set autot traceonly
SQL> select * from big_emp a,dept_new b where a.deptno=b.deptno;
917504 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1925493178
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 917K| 54M| 1490 (2)| 00:00:18 |
|* 1 | HASH JOIN | | 917K| 54M| 1490 (2)| 00:00:18 |
| 2 | TABLE ACCESS FULL| DEPT_NEW | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| BIG_EMP | 917K| 28M| 1482 (1)| 00:00:18 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
1 db block gets
66338 consistent gets
0 physical reads
0 redo size
62512398 bytes sent via SQL*Net to client
673349 bytes received via SQL*Net from client
61168 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
917504 rows processedSort Merge Joins
排序合并連接與嵌套循環(huán)和散列連接都不同，排序合并連接沒(méi)有驅(qū)動(dòng)表的概念。簡(jiǎn)言之，排序合并將依次處理排序第一個(gè)輸入集，排序第二個(gè)輸入集，然后合并結(jié)果。排序合并通常不如散列高效，因?yàn)閮蓚€(gè)結(jié)果集都需要排序，而散列連接在數(shù)據(jù)輸出前，只需處理一個(gè)結(jié)果集。排序合并通常在非等值連接中有效。即連接條件不是一個(gè)等式而是范圍比較（<或者>=）. 或者是兩個(gè)表的數(shù)據(jù)已經(jīng)排好序啦。
我們看如下例子
