Posted on 2009-01-19 12:58
Astro.Qi 閱讀(360)
評論(0) 編輯 收藏 所屬分類:
Oracle
只有對這些問題有了清晰的理解后，我們才能針對特定的查詢需求選擇合適的連接方式，開發(fā)出健壯的數(shù)據(jù)庫應(yīng)用程序。選擇合適的表連接方法對SQL語句運行的性能有著至關(guān)重要的影響。下面我們就Oracle常用的一些連接方法及適用情景做一個簡單的介紹。
一、嵌套循環(huán)連接（Nested Loop）
嵌套循環(huán)連接的工作方式是這樣的：
1、Oracle首先選擇一張表作為連接的驅(qū)動表，這張表也稱為外部表（Outer Table）。由驅(qū)動表進行驅(qū)動連接的表或數(shù)據(jù)源稱為內(nèi)部表（Inner Table）。
2、提取驅(qū)動表中符合條件的記錄，與被驅(qū)動表的連接列進行關(guān)聯(lián)查詢符合條件的記錄。在這個過程中，Oracle首先提取驅(qū)動表中符合條件的第一條記錄，再與內(nèi)部表的連接列進行關(guān)聯(lián)查詢相應(yīng)的記錄行。在關(guān)聯(lián)查詢的過程中，Oracle會持續(xù)提取驅(qū)動表中其他符合條件的記錄與內(nèi)部表關(guān)聯(lián)查詢。這兩個過程是并行進行的，因此嵌套循環(huán)連接返回前幾條記錄的速度是非常快的。在這里需要說明的是，由于Oracle最小的IO單位為單個數(shù)據(jù)塊，因此在這個過程中 Oracle會首先提取驅(qū)動表中符合條件的單個數(shù)據(jù)塊中的所有行，再與內(nèi)部表進行關(guān)聯(lián)連接查詢的，然后提取下一個數(shù)據(jù)塊中的記錄持續(xù)地循環(huán)連接下去。當(dāng)然，如果單行記錄跨越多個數(shù)據(jù)塊的話，就是一次單條記錄進行關(guān)聯(lián)查詢的。
3、嵌套循環(huán)連接的過程如下所示：
NESTED LOOP
<Outer Loop>
<Inner Loop>
|
我們可以看出這里面存在著兩個循環(huán)，一個是外部循環(huán)，提取驅(qū)動表中符合條件的每條記錄。另外一個是內(nèi)部循環(huán)，根據(jù)外循環(huán)中提取的每條記錄對內(nèi)部表進行連接查詢相應(yīng)的記錄。由于這兩個循環(huán)是嵌套進行的，故此種連接方法稱為嵌套循環(huán)連接。
嵌套循環(huán)連接適用于查詢的選擇性強、約束性高并且僅返回小部分記錄的結(jié)果集。通常要求驅(qū)動表的記錄（符合條件的記錄，通常通過高效的索引訪問）較少，且被驅(qū)動表連接列有唯一索引或者選擇性強的非唯一索引時，嵌套循環(huán)連接的效率是比較高的。比如下面這個查詢是選用嵌套循環(huán)連接的典型例子：
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)
|
在這個查詢中，優(yōu)化器選擇emp作為驅(qū)動表，根據(jù)唯一性索引PK_EMP快速返回符合條件empno為7900的記錄，然后再與被驅(qū)動表dept的 deptno關(guān)聯(lián)查詢相應(yīng)的dname并最終返回結(jié)果集。由于dept表上面的deptno有唯一索引PK_DEPT，故查詢能夠快速地定位deptno 對應(yīng)dname為SALES的記錄并返回。
嵌套循環(huán)連接驅(qū)動表的選擇也是連接中需要著重注意的一點，有一個常見的誤區(qū)是驅(qū)動表要選擇小表，其實這是不對的。假如有兩張表A、B關(guān)聯(lián)查詢，A表有1000000條記錄，B表有10000條記錄，但是A表過濾出來的記錄只有10條，這時候顯然用A表當(dāng)做驅(qū)動表是比較合適的。因此驅(qū)動表是由過濾條件限制返回記錄最少的那張表，而不是根據(jù)表的大小來選擇的。
在外連接查詢中，如果走嵌套循環(huán)連接的話，那么驅(qū)動表必然是沒有符合條件關(guān)聯(lián)的那張表，也就是后面不加(+)的那張表。這是由于外連接需要提取可能另一張表沒符合條件的記錄，因此驅(qū)動表需要是那張我們要返回所有符合條件記錄的表。比如下面這個查詢，就是選擇了emp表做為驅(qū)動表進行連接：
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
|
嵌套循環(huán)連接返回前幾行的記錄是非常快的，這是因為使用了嵌套循環(huán)后，不需要等到全部循環(huán)結(jié)束再返回結(jié)果集，而是不斷地將查詢出來的結(jié)果集返回。在這種情況下，終端用戶將會快速地得到返回的首批記錄，且同時等待Oracle內(nèi)部處理其他記錄并返回。如果查詢的驅(qū)動表的記錄數(shù)非常多，或者被驅(qū)動表的連接列上無索引或索引不是高度可選的情況，嵌套循環(huán)連接的效率是非常低的。
二、排序合并連接(Sort Merge)
排序合并連接的方法非常簡單。在排序合并連接中是沒有驅(qū)動表的概念的，兩個互相連接的表按連接列的值先排序，排序完后形成的結(jié)果集再互相進行合并連接提取符合條件的記錄。相比嵌套循環(huán)連接，排序合并連接比較適用于返回大數(shù)據(jù)量的結(jié)果。以下為排序合并連接的例子：
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先排序，然后排序好的結(jié)果集再進行合并連接返回最終的記錄。
排序合并連接在數(shù)據(jù)表預(yù)先排序好的情況下效率是非常高的，也比較適用于非等值連接的情況，比如>、>=、<=等情況下的連接（哈希連接只適用于等值連接）。由于Oracle中排序操作的開銷是非常消耗資源的，當(dāng)結(jié)果集很大時排序合并連接的性能很差，于是Oracle在7.3之后推出了新的連接方式——哈希連接。
三、哈希連接（Hash join）
哈希連接分為兩個階段，如下。
1、構(gòu)建階段：優(yōu)化器首先選擇一張小表做為驅(qū)動表，運用哈希函數(shù)對連接列進行計算產(chǎn)生一張哈希表。通常這個步驟是在內(nèi)存（hash_area_size）里面進行的，因此運算很快。
2、探測階段：優(yōu)化器對被驅(qū)動表的連接列運用同樣的哈希函數(shù)計算得到的結(jié)果與前面形成的哈希表進行探測返回符合條件的記錄。這個階段中如果被驅(qū)動表的連接列的值沒有與驅(qū)動表連接列的值相等的話，那么這些記錄將會被丟棄而不進行探測。關(guān)于哈希連接更深層次的原理可以參考Itpub上網(wǎng)友logzgh發(fā)表的“hash join算法原理”帖子（http://www.itpub.net/showthread.php?threadid=315494）。
以下為哈希連接的一個例子：
Roby@XUE> select /**//*+ use_hash(emp,dept) */ emp.ename,dept.dname
2 from emp,dept
3 where emp.deptno=dept.deptno;
ENAME DNAME
---------- --------------
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
12 rows selected.
Execution Plan
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 264 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 12 | 264 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 12 | 108 | 3 (0)| 00:00:01 |
|
在這個查詢中優(yōu)化器首先選擇dept這張表為驅(qū)動表，對列deptno運算哈希函數(shù)構(gòu)建一張哈希表，然后再對被驅(qū)動表emp的deptno列運算同樣的哈希函數(shù)計算得到的結(jié)果進行探測，最終連接得出符合條件的記錄。
同嵌套循環(huán)外連接一樣，哈希循環(huán)外連接的驅(qū)動表同樣是沒有符合條件關(guān)聯(lián)的那張表。如下述例子：
Roby@XUE> select /**//*+ use_hash(emp,dept) */ emp.ename,dept.dname
2 from emp,dept
3 where emp.deptno=dept.deptno(+);
ENAME DNAME
---------- --------------
MILLER ACCOUNTING
KING ACCOUNTING
CLARK ACCOUNTING
FORD RESEARCH
ADAMS RESEARCH
SCOTT RESEARCH
JONES RESEARCH
JAMES SALES
TURNER SALES
BLAKE SALES
MARTIN SALES
WARD SALES
ALLEN
SMITH
12 rows selected.
Execution Plan
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 7 (15)| 00:00:01 |
|* 1| HASH JOIN OUTER | | 14 | 308 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0) | 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0) | 00:00:01 |
--------------------------------------------------------------
|
哈希連接比較適用于返回大數(shù)據(jù)量結(jié)果集的連接。使用哈希連接必須是在CBO模式下，參數(shù)hash_join_enabled設(shè)置為true，且只適用于等值連接。從Oracle9i開始，哈希連接由于其良好的性能漸漸取代了原來的排序合并連接。
四、跟表連接有關(guān)的幾個HINT
（1）use_nl（t1,t2）：表示對表t1、t2關(guān)聯(lián)時采用嵌套循環(huán)連接。 （2）use_merge（t1,t2）：表示對表t1、t2關(guān)聯(lián)時采用排序合并連接。 （3）use_hash（t1,t2）：表示對表t1、t2關(guān)聯(lián)時采用哈希連接。 （4）leading(t)：表示在進行表連接時，選擇t為驅(qū)動表。 （5）ordred：要求優(yōu)化器按from列出的表順序進行連接。
需要注意的是在Oracle使用hint時,如果SQL語句中表用別名的話,那么hint中必須使用表的別名,否則hint將不會生效。