資料庫表的連線方式及用法（一）

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

      1. 相等連線

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

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

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

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

　　SELECT * FROM EMP,DEPT

　　WHERE EMP.DEPTNO = DEPT.DEPTNO;

　　2. 外連線

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

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

　　A) 左條件(+) = 右條件；

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

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

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

　　B) 左條件 = 右條件(+)；

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

　　此時也稱為"左外連線"。

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

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

　　--顯示員工資訊以及所對應的部門資訊，不顯示沒有部門的員工資訊

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

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

　　--顯示員工資訊以及所對應的部門資訊，不顯示沒有員工的部門資訊

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

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

       3. 不等連線

　　兩個表中的相關的兩列進行不等連線，比較符號一般為>、

　　例 顯示員工的編號、姓名、工資，以及工資所對應的級別。

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

　　WHERE EMP.SAL BETWEEN LOSAL AND HISAL;

　　例 顯示僱員的編號、姓名、工資、工資級別、所在部門的名稱。

　　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到記憶體中。當表的分割槽超過了可用記憶體時，分割槽的部分資料就會臨時地寫到磁碟上的臨時表空間上。因此，分割槽的資料寫磁碟時，比較大的區間(EXTENT)會提高I/O效能。ORACLE推薦的臨時表空間的區間是1MB。臨時表空間的區間大小由UNIFORM. SIZE指定。

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

　　1) 第二個大表進行掃描

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

　　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 ptimizer=CHOOSE (Cost=5 =82 Bytes=3936 　　) 　　1 0 HASH JOIN (Cost=5 Card=82 Bytes=3936) 　　2 1 ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes 　　=1968) 　　3 1 TABLE (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_樹索引更加迅速。