一個(gè)SQL語(yǔ)句的關(guān)聯(lián)表超過(guò)兩個(gè)，那么連接的順序如何呢?ORACLE首先連接其中的兩個(gè)表，產(chǎn)生一個(gè)結(jié)果集;然后將產(chǎn)生的結(jié)果集與下一個(gè)表再進(jìn)行關(guān)聯(lián);繼續(xù)這個(gè)過(guò)程，直到所有的表都連接完成;***產(chǎn)生所需的數(shù)據(jù)，,本文將主要從以下幾個(gè)典型的例子來(lái)分析Oracle表的幾種不同連接方式:

1. 相等連接

通過(guò)兩個(gè)表具有相同意義的列，可以建立相等連接條件。

只有連接列上在兩個(gè)表中都出現(xiàn)且值相等的行才會(huì)出現(xiàn)在查詢結(jié)果中。

例 查詢員工信息以及對(duì)應(yīng)的員工所在的部門信息;

   SELECT * FROM EMP,DEPT;   
　　SELECT * FROM EMP,DEPT  
 
　　WHERE EMP.DEPTNO = DEPT.DEPTNO;  

REM 顯示工資超過(guò)2000的員工信息以及對(duì)應(yīng)的員工的部門名稱。

2. 外連接

對(duì)于外連接，Oracle中可以使用“(+)”來(lái)表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN，下面將配合實(shí)例一一介紹。除了顯示匹配相等連接條件的信息之外,還顯示無(wú)法匹配相等連接條件的某個(gè)表的信息。

外連接采用(+)來(lái)識(shí)別。

◆左條件(+) = 右條件;

代表除了顯示匹配相等連接條件的信息之外,還顯示右條件所在的表中無(wú)法匹配相等連接條件的信息。

此時(shí)也稱為"右外連接".另一種表示方法是:

SELECT ... FROM 表1 RIGHT OUTER JOIN 表2 ON 連接條件

◆左條件 = 右條件(+);

代表除了顯示匹配相等連接條件的信息之外,還顯示左條件所在的表中無(wú)法匹配相等連接條件的信息。

此時(shí)也稱為"左外連接".

SELECT ... FROM 表1 LEFT OUTER JOIN 表2 ON 連接條件

例 顯示員工信息以及所對(duì)應(yīng)的部門信息

--無(wú)法顯示沒(méi)有部門的員工信息

--無(wú)法顯示沒(méi)有員工的部門信息

--SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

--直接做相等連接:

SELECT * FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

REM 顯示員工信息以及所對(duì)應(yīng)的部門信息,顯示沒(méi)有員工的部門信息

--SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO(+) = DEPT.DEPTNO;

SELECT * FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

REM 顯示員工信息以及所對(duì)應(yīng)的部門信息,顯示沒(méi)有部門的員工信息

--SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO(+);

SELECT * FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

3. 不等連接

兩個(gè)表中的相關(guān)的兩列進(jìn)行不等連接,比較符號(hào)一般為>,<,...,BETWEEN.. AND..

REM SALGRADE

--DESC SALGRADE;

--SELECT * FROM SALGRADE;

REM 顯示員工的編號(hào),姓名,工資,以及工資所對(duì)應(yīng)的級(jí)別。

SELECT EMPNO,ENAME,SAL,SALGRADE.* FROM SALGRADE,EMP

WHERE EMP.SAL BETWEEN LOSAL AND HISAL;

REM 顯示雇員的編號(hào),姓名,工資,工資級(jí)別,所在部門的名稱;

SELECT EMPNO,ENAME,SAL,GRADE,DNAME FROM EMP,DEPT,SALGRADE

WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.SAL BETWEEN LOSAL AND HISAL;

4. 自連接

自連接是數(shù)據(jù)庫(kù)中經(jīng)常要用的連接方式，使用自連接可以將自身表的一個(gè)鏡像當(dāng)作另一個(gè)表來(lái)對(duì)待，從而能夠得到一些特殊的數(shù)據(jù)。下面介紹一下自連接的方法:

將原表復(fù)制一份作為另一個(gè)表,兩表做笛卡兒相等連接。

例 顯示雇員的編號(hào),名稱,以及該雇員的經(jīng)理名稱

SELECT WORKER.ENAME,WORKER.MGR,MANAGER.EMPNO,MANAGER.ENAME FROM EMP WORKER,EMP MANAGER

WHERE WORKER.MGR = MANAGER.EMPNO;

5.哈希連接

當(dāng)內(nèi)存能夠提供足夠的空間時(shí),哈希(HASH)連接是Oracle優(yōu)化器通常的選擇。哈希連接中，優(yōu)化器根據(jù)統(tǒng)計(jì)信息，首先選擇兩個(gè)表中的小表，在內(nèi)存中建立這張表的基于連接鍵的哈希表;優(yōu)化器再掃描表連接中的大表，將大表中的數(shù)據(jù)與哈希表進(jìn)行比較，如果有相關(guān)聯(lián)的數(shù)據(jù)，則將數(shù)據(jù)添加到結(jié)果集中。

當(dāng)表連接中的小表能夠完全cache到可用內(nèi)存的時(shí)候，哈希連接的效果***。哈希連接的成本只是兩個(gè)表從硬盤讀入到內(nèi)存的成本。

但是，如果哈希表過(guò)大而不能全部cache到可用內(nèi)存時(shí)，優(yōu)化器將會(huì)把哈希表分成多個(gè)分區(qū)，再將分區(qū)逐一cache到內(nèi)存中。當(dāng)表的分區(qū)超過(guò)了可用內(nèi)存時(shí)，分區(qū)的部分?jǐn)?shù)據(jù)就會(huì)臨時(shí)地寫到磁盤上的臨時(shí)表空間上。因此，分區(qū)的數(shù)據(jù)寫磁盤時(shí)，比較大的區(qū)間(EXTENT)會(huì)提高I/O性能。ORACLE推薦的臨時(shí)表空間的區(qū)間是1MB。臨時(shí)表空間的區(qū)間大小由UNIFORM SIZE指定。

當(dāng)哈希表構(gòu)建完成后，進(jìn)行下面的處理：

1) 第二個(gè)大表進(jìn)行掃描

2) 如果大表不能完全cache到可用內(nèi)存的時(shí)候，大表同樣會(huì)分成很多分區(qū)

3) 大表的***個(gè)分區(qū)cache到內(nèi)存

4) 對(duì)大表***個(gè)分區(qū)的數(shù)據(jù)進(jìn)行掃描，并與哈希表進(jìn)行比較，如果有匹配的紀(jì)錄，添加到結(jié)果集里面

5) 與***個(gè)分區(qū)一樣，其它的分區(qū)也類似處理。

6) 所有的分區(qū)處理完后，ORACLE對(duì)產(chǎn)生的結(jié)果集進(jìn)行歸并，匯總，產(chǎn)生最終的結(jié)果。

當(dāng)哈希表過(guò)大或可用內(nèi)存有限，哈希表不能完全CACHE到內(nèi)存。隨著滿足連接條件的結(jié)果集的增加，可用內(nèi)存會(huì)隨之下降，這時(shí)已經(jīng)CACHE到內(nèi)存的數(shù)據(jù)可能會(huì)重新寫回到硬盤去。如果出現(xiàn)這種情況，系統(tǒng)的性能就會(huì)下降。

當(dāng)連接的兩個(gè)表是用等值連接并且表的數(shù)據(jù)量比較大時(shí)，優(yōu)化器才可能采用哈希連接。哈希連接是基于CBO的。只有在數(shù)據(jù)庫(kù)初始化參數(shù)HASH_JOIN_ENABLED設(shè)為True,并且為參數(shù)PGA_AGGREGATE_TARGET設(shè)置了一個(gè)足夠大的值的時(shí)候,Oracle才會(huì)使用哈希邊連接。HASH_AREA_SIZE是向下兼容的參數(shù),但在Oracle9i之前的版本中應(yīng)當(dāng)使用HASH_AREA_SIZE。當(dāng)使用ORDERED提示時(shí),FROM子句中的***張表將用于建立哈希表。

   select a.user_name,b.dev_no   
　　from user_info a, dev_info b  
 
　　where a.user_id = b.user_id;  
 
　　Plan  
 
　　----------------------------------------------------------  
 
　　0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=3936  
 
　　)  
 
　　1 0 HASH JOIN (Cost=5 Card=82 Bytes=3936)  
 
　　2 1 TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes  
 
　　=1968)  
 
　　3 1 TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=  
 
　　1968)  

可以通過(guò)在SQL語(yǔ)句中添加HINTS,強(qiáng)制ORACLE優(yōu)化器產(chǎn)生哈希連接的執(zhí)行計(jì)劃。

select /*+ use_hash(a b)*/ a.user_name,b.dev_no   
 
from user_info a, dev_info b  
 
where a.user_id = b.user_id; 

當(dāng)缺少有用的索引時(shí)，哈希連接比嵌套循環(huán)連接更加有效。哈希連接也可能比嵌套循環(huán)連接更快，因?yàn)樘幚韮?nèi)存中的哈希表比檢索B_樹索引更加迅速。

【編輯推薦】

1. MySQL左連接、右連接和內(nèi)連接詳解
2. 數(shù)據(jù)庫(kù)連接性能深度探索
3. ADO.NET數(shù)據(jù)庫(kù)連接、操作SQL舉例a>
4. 如何處理幾種常見(jiàn)的數(shù)據(jù)庫(kù)不當(dāng)連接
5. 多數(shù)據(jù)庫(kù)環(huán)境的連接策略