Oracle中表的連線及其調整(一)

在日常基於資料庫應用的開發過程中，我們經常需要對多個表或者資料來源進行關聯查詢而得出我們需要的結果集。那麼Oracle到底存在著哪幾種連線方式？最佳化器內部又是怎樣處理這些連線的？哪種連線方式又是適合哪種查詢需求的？只有對這些問題有了清晰的理解後，我們才能針對特定的查詢需求選擇合適的連線方式，開發出健壯的資料庫應用程式。選擇合適的表連線方法對SQL語句執行的效能有著至關重要的影響。下面我們就Oracle常用的一些連線方法及適用情景做一個簡單的介紹。
   
一、 巢狀迴圈連線（Nested Loop）

巢狀迴圈連線的工作方式是這樣的：
1、 Oracle首先選擇一張表作為連線的驅動表，這張表也稱為外部表（Outer Table）。由驅動表進行驅動連線的表或資料來源稱為內部表（Inner Table）。
2、 提取驅動表中符合條件的記錄，與被驅動表的連線列進行關聯查詢符合條件的記錄。在這個過程中，Oracle首先提取驅動表中符合條件的第一條記錄，再與內部表的連線列進行關聯查詢相應的記錄行。在關聯查詢的過程中，Oracle會持續提取驅動表中其他符合條件的記錄與內部表關聯查詢。這兩個過程是並行進行的，因此巢狀迴圈連線返回前幾條記錄的速度是非常快的。在這裡需要說明的是，由於Oracle最小的IO單位為單個資料塊，因此在這個過程中Oracle會首先提取驅動表中符合條件的單個資料塊中的所有行，再與內部表進行關聯連線查詢的，然後提取下一個資料塊中的記錄持續地迴圈連線下去。當然，如果單行記錄跨越多個資料塊的話，就是一次單條記錄進行關聯查詢的。
3、 巢狀迴圈連線的過程如下所示：

 NESTED LOOP
 <Outer Loop>
 <Inner Loop>
我們可以看出這裡面存在著兩個迴圈，一個是外部迴圈，提取驅動表中符合條件的每條記錄。另外一個是內部迴圈，根據外迴圈中提取的每條記錄對內部表進行連線查詢相應的記錄。由於這兩個迴圈是巢狀進行的，故此種連線方法稱為巢狀迴圈連線。

巢狀迴圈連線適用於查詢的選擇性強、約束性高並且僅返回小部分記錄的結果集。通常要求驅動表的記錄（符合條件的記錄，通常透過高效的索引訪問）較少，且被驅動表連線列有唯一索引或者選擇性強的非唯一索引時，巢狀迴圈連線的效率是比較高的。比如下面這個查詢是選用巢狀迴圈連線的典型例子：
SQL> select e.empno,e.ename,e.job,d.dname
2 from emp e,dept d
3 where e.deptno=d.deptno
4 and e.empno=7900;

EMPNO ENAME JOB DNAME
---------- ---------- --------- --------------
7900 JAMES CLERK SALES

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
5 4 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) 

    在這個查詢中，最佳化器選擇emp作為驅動表，根據唯一性索引PK_EMP快速返回符合條件empno為7900的記錄，然後再與被驅動表dept的deptno關聯查詢相應的dname並最終返回結果集。由於dept表上面的deptno有唯一索引PK_DEPT，故查詢能夠快速地定位deptno對應dname為SALES的記錄並返回。
巢狀迴圈連線驅動表的選擇也是連線中需要著重注意的一點，有一個常見的誤區是驅動表要選擇小表，其實這是不對的。假如有兩張表A、B關聯查詢，A表有1000000條記錄，B表有10000條記錄，但是A表過濾出來的記錄只有10條，這時候顯然用A表當做驅動表是比較合適的。因此驅動表是由過濾條件限制返回記錄最少的那張表，而不是根據表的大小來選擇的。
在外連線查詢中，如果走巢狀迴圈連線的話，那麼驅動表必然是沒有符合條件關聯的那張表，也就是後面不加(+)的那張表。這是由於外連線需要提取可能另一張表沒符合條件的記錄，因此驅動表需要是那張我們要返回所有符合條件記錄的表。比如下面這個查詢，就是選擇了emp表做為驅動表進行連線： 

Roby@XUE> select emp.ename,dept.dname
2  from emp,dept
3  where emp.deptno=dept.deptno(+);
ENAME      DNAME
---------- --------------
SMITH
ALLEN
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING
14 rows selected.
Execution Plan
----------------------------------------------------------

|   0 | SELECT STATEMENT             |         |    14 |   308 |    15
|   1 |  NESTED LOOPS OUTER          |         |    14 |   308 |    15
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   126 |     3
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1
|*  4 |    INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0
    巢狀迴圈連線返回前幾行的記錄是非常快的，這是因為使用了巢狀迴圈後，不需要等到全部迴圈結束再返回結果集，而是不斷地將查詢出來的結果集返回。在這種情況下，終端使用者將會快速地得到返回的首批記錄，且同時等待Oracle內部處理其他記錄並返回。如果查詢的驅動表的記錄數非常多，或者被驅動表的連線列上無索引或索引不是高度可選的情況，巢狀迴圈連線的效率是非常低的。

二、 排序合併連線(Sort Merge)
   排序合併連線的方法非常簡單。在排序合併連線中是沒有驅動表的概念的，兩個互相連線的表按連線列的值先排序，排序完後形成的結果集再互相進行合併連線提取符合條件的記錄。相比巢狀迴圈連線，排序合併連線比較適用於返回大資料量的結果。以下為排序合併連線的例子：

 Roby@XUE> select emp.ename,dept.dname
2 from emp,dept
3 where emp.deptno=dept.deptno
4 /

ENAME DNAME
---------- --------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
SCOTT RESEARCH
FORD RESEARCH
ADAMS RESEARCH
TURNER SALES
JAMES SALES
WARD SALES
MARTIN SALES
BLAKE SALES

12 rows selected.

Execution Plan

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 264 | 8 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 12 | 264 | 8 (25)| 00:00:01 |
| 2 | SORT JOIN | | 4 | 52 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 12 | 108 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP | 12 | 108 | 3 (0)| 00:00:01 |

    可以看得出來上述查詢首先按dept、emp兩張表的deptno先排序，然後排序好的結果集再進行合併連線返回最終的記錄。
排序合併連線在資料表預先排序好的情況下效率是非常高的，也比較適用於非等值連線的情況，比如>、>=、<=等情況下的連線（雜湊連線只適用於等值連線）。由於Oracle中排序操作的開銷是非常消耗資源的，當結果集很大時排序合併連線的效能很差，於是Oracle在7.3之後推出了新的連線方式——雜湊連線。