Oracle資料庫表串連方式及常見用法

一個SQL語句的關聯表超過兩個，那麼串連的順序如何呢?ORACLE首先串連其中的兩個表，產生一個結果集;然後將產生的結果集與下一個表再進行關聯;繼續這個過程，直到所有的表都串連完成;最後產生所需的資料，,本文將主要從以下幾個典型的例子來分析Oracle表的幾種不同串連方式:

1. 相等串連

通過兩個表具有相同意義的列，可以建立相等串連條件。

只有串連列上在兩個表中都出現且值相等的行才會出現在查詢結果中。

例 查詢員工資訊以及對應的員工所在的部門資訊;

 

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

REM 顯示工資超過2000的員工資訊以及對應的員工的部門名稱。

2. 外串連

對於外串連，Oracle中可以使用“(+)”來表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN，下面將配合執行個體一一介紹。除了顯示匹配相等串連條件的資訊之外,還顯示無法匹配相等串連條件的某個表的資訊。

外串連採用(+)來識別。

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

代表除了顯示匹配相等串連條件的資訊之外,還顯示右條件所在的表中無法匹配相等串連條件的資訊。

此時也稱為"右外串連".另一種表示方法是:

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

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

代表除了顯示匹配相等串連條件的資訊之外,還顯示左條件所在的表中無法匹配相等串連條件的資訊。

此時也稱為"左外串連".

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

例 顯示員工資訊以及所對應的部門資訊

--無法顯示沒有部門的員工資訊

--無法顯示沒有員工的部門資訊

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

--直接做相等串連:

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

REM 顯示員工資訊以及所對應的部門資訊,顯示沒有員工的部門資訊

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

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

REM 顯示員工資訊以及所對應的部門資訊,顯示沒有部門的員工資訊

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

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

3. 不等串連

兩個表中的相關的兩列進行不等串連,比較符號一般為>,<,...,BETWEEN.. AND..

REM SALGRADE

--DESC SALGRADE;

--SELECT * FROM SALGRADE;

REM 顯示員工的編號,姓名,工資,以及工資所對應的層級。

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

WHERE EMP.SAL BETWEEN LOSAL AND HISAL;

REM 顯示僱員的編號,姓名,工資,工資層級,所在部門的名稱;

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

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

4. 自串連

自串連是資料庫中經常要用的串連方式，使用自串連可以將自身表的一個鏡像當作另一個表來對待，從而能夠得到一些特殊的資料。下面介紹一下自串連的方法:

將原表複製一份作為另一個表,兩表做笛卡兒相等串連。

例 顯示僱員的編號,名稱,以及該僱員的經理名稱

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

WHERE WORKER.MGR = MANAGER.EMPNO;

5.雜湊串連

當記憶體能夠提供足夠的空間時,雜湊(HASH)串連是Oracle最佳化器通常的選擇。雜湊串連中，最佳化器根據統計資訊，首先選擇兩個表中的小表，在記憶體中建立這張表的基於串連鍵的雜湊表;最佳化器再掃描表串連中的大表，將大表中的資料與雜湊表進行比較，如果有相關聯的資料，則將資料添加到結果集中。

當表串連中的小表能夠完全cache到可用記憶體的時候，雜湊串連的效果最佳。雜湊串連的成本只是兩個表從硬碟讀入到記憶體的成本。

但是，如果雜湊表過大而不能全部cache到可用記憶體時，最佳化器將會把雜湊表分成多個分區，再將分區逐一cache到記憶體中。當表的分區超過了可用記憶體時，分區的部分資料就會臨時地寫到磁碟上的暫存資料表空間上。因此，分區的資料寫磁碟時，比較大的區間(EXTENT)會提高I/O效能。ORACLE推薦的暫存資料表空間的區間是1MB。暫存資料表空間的區間大小由UNIFORM SIZE指定。

當雜湊表構建完成後，進行下面的處理：

1) 第二個大表進行掃描

2) 如果大表不能完全cache到可用記憶體的時候，大表同樣會分成很多分區

3) 大表的第一個分區cache到記憶體

4) 對大表第一個分區的資料進行掃描，並與雜湊表進行比較，如果有匹配的紀錄，添加到結果集裡面

5) 與第一個分區一樣，其它的分區也類似處理。

6) 所有的分區處理完後，ORACLE對產生的結果集進行歸併，匯總，產生最終的結果。

當雜湊表過大或可用記憶體有限，雜湊表不能完全CACHE到記憶體。隨著滿足串連條件的結果集的增加，可用記憶體會隨之下降，這時已經CACHE到記憶體的資料可能會重新寫回到硬碟去。如果出現這種情況，系統的效能就會下降。

當串連的兩個表是用等值串連並且表的資料量比較大時，最佳化器才可能採用雜湊串連。雜湊串連是基於CBO的。只有在資料庫初始化參數HASH_JOIN_ENABLED設為True,並且為參數PGA_AGGREGATE_TARGET設定了一個足夠大的值的時候,Oracle才會使用雜湊邊串連。HASH_AREA_SIZE是向下相容的參數,但在Oracle9i之前的版本中應當使用HASH_AREA_SIZE。當使用ORDERED提示時,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)  
 

可以通過在SQL語句中添加HINTS,強制ORACLE最佳化器產生雜湊串連的執行計畫。

 

  
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; 
 

當缺少有用的索引時，雜湊串連比嵌套迴圈串連更加有效。雜湊串連也可能比嵌套迴圈串連更快，因為處理記憶體中的雜湊表比檢索B_樹索引更加迅速。

1. MySQL左串連、右串連和內串連詳解
2. 資料庫連接效能深度探索
3. ADO.NET資料庫連接、操作SQL舉例a>
4. 如何處理幾種常見的資料庫不當串連
5. 多資料庫環境的串連策略