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