Oracle中表的連接及其調(diào)整

1、Oracle中表的連接及其調(diào)整只有對這些問題有了清晰的理解后，我們才能針對特定的查詢需求選擇合適的連 接方式，開發(fā)出健壯的數(shù)據(jù)庫應(yīng)用程序。選擇合適的表連接方法對SQL語句運(yùn)行 的性能有著至關(guān)重要的影響。下面我們就Oracle常用的一些連接方法及適用情景做一個簡單的介紹。常用的表連接方式：a.嵌套循環(huán)連接(Nested Loop) b.排序合并連接(Sort Merge) c. 哈希連接(Hash join )一、嵌套循環(huán)連接(Nested Loop)嵌套循環(huán)連接的工作方式是這樣的：1、 Oracle首先選擇一張表作為連接的驅(qū)動表，這張表也稱為外部表( Outer Table )。由驅(qū)動表進(jìn)行驅(qū)

2、動連接的表或數(shù)據(jù)源稱為內(nèi)部表(Inner Table )。2、提取驅(qū)動表中符合條件的記錄，與被驅(qū)動表的連接列進(jìn)行關(guān)聯(lián)查詢符合條件的記錄。在這個過程中，Oracle首先提取驅(qū)動表中符合條件的第一條記錄，再 與內(nèi)部表的連接列進(jìn)行關(guān)聯(lián)查詢相應(yīng)的記錄行。在關(guān)聯(lián)查詢的過程中，Oracle會持續(xù)提取驅(qū)動表中其他符合條件的記錄與內(nèi)部表關(guān)聯(lián)查詢。這兩個過程是并行進(jìn)行的，因此嵌套循環(huán)連接返回前幾條記錄的速度是非?？斓摹Ｔ谶@里需要說明的是，由于Oracle最小的IO單位為單個數(shù)據(jù)塊，因此在這個過程中Oracle會首先提取驅(qū)動表中符合條件的單個數(shù)據(jù)塊中的所有行，再與內(nèi)部表進(jìn)行關(guān)聯(lián)連接查詢的，然后提取下一個數(shù)據(jù)塊中的

3、記錄持續(xù)地循環(huán)連接下去。當(dāng)然，如果單行記錄跨越多個數(shù)據(jù)塊的話，就是一次單條記錄進(jìn)行關(guān)聯(lián)查詢的。3、嵌套循環(huán)連接的過程如下所示:NESTED LOOP<Outer Loop>vlnner Loop>我們可以看出這里面存在著兩個循環(huán)， 一個是外部循環(huán)，提取驅(qū)動表中符合條件 的每條記錄。另外一個是內(nèi)部循環(huán)，根據(jù)外循環(huán)中提取的每條記錄對內(nèi)部表進(jìn)行 連接查詢相應(yīng)的記錄。由于這兩個循環(huán)是嵌套進(jìn)行的，故此種連接方法稱為嵌套 循環(huán)連接。嵌套循環(huán)連接適用于查詢的選擇性強(qiáng)、約束性高并且僅返回小部分記錄的結(jié)果 集。通常要求驅(qū)動表的記錄（符合條件的記錄，通常通過高效的索引訪問）較少, 且被驅(qū)動表連接

4、列有唯一索引或者選擇性強(qiáng)的非唯一索引時，嵌套循環(huán)連接的效率是比較高的。比如下面這個查詢是選用嵌套循環(huán)連接的典型例子：SQL> select e.emp no ,e.e name,e.job,d.d name2 from emp e,dept d3 where e.dept no=d.dept no4 and e.emp no=7900;EMPNO ENAME JOB DNAME7900 JAMES CLERK SALESExecuti on Pla n0 SELECT STATEMENT Optimizer=CHOOSE1 0 NESTED LOOPS2 1 TABLE ACCESS (

5、BY INDEX ROWID) OF 'EMP'3 2 INDEX (UNIQUE SCAN) OF 卩 K_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_EM快速返回 符合條件emp no為7900的記錄，然后再與被驅(qū)動表dept的dept no關(guān)聯(lián)查詢相 應(yīng)的dn ame并最終返回結(jié)果集。由于dept表上面的dept

6、no有唯一索引PK_DEPT 故查詢能夠快速地定位dept no對應(yīng)dn ame為SALES的記錄并返回。嵌套循環(huán)連接驅(qū)動表的選擇也是連接中需要著重注意的一點(diǎn)，有一個常見的誤區(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)的那張表，也就是后面不加（+）的那張表。這是由于外連接需要提取可能另一 張表沒符

7、合條件的記錄，因此驅(qū)動表需要是那張我們要返回所有符合條件記錄的 表。比如下面這個查詢，就是選擇了 emp表做為驅(qū)動表進(jìn)行連接：RobyXUE5elect emp.e name,dept.d name2 from emp,dept3 where emp.dept no=dept.dept no (+);ENAMEDNAMESMITHALLENWARDSALESJONESRESEARCHMARTINSALESBLAKESALESCLARKACCOUNTINGSCOTTRESEARCHKINGACCOUNTINGTURNERSALESADAMSRESEARCHJAMESSALESFORDRESEAR

8、CHMILLERACCOUNTING14 rows selected.Executio n Pla n|0|SELECTSTATEMENT|114|308 |15|1 |NESTED LOOPSOUTER|114 |308|15| 2 |TABLE ACCESS FULL1EMP|14 |126 |3|3 |TABLEACCESBYINDEXROWIDDEPT11|13 |1|*4 |INDEX UNIQUE SCAN|DEPT_PK| 1 |1 0嵌套循環(huán)連接返回前幾行的記錄是非?？斓?， 這是因為使用了嵌套循環(huán)后，不需 要等到全部循環(huán)結(jié)束再返回結(jié)果集， 而是不斷地將查詢出來的結(jié)果集返回。 在

9、這 種情況下，終端用戶將會快速地得到返回的首批記錄，且同時等待 Oracle內(nèi)部 處理其他記錄并返回。如果查詢的驅(qū)動表的記錄數(shù)非常多， 或者被驅(qū)動表的連接 列上無索引或索引不是高度可選的情況，嵌套循環(huán)連接的效率是非常低的。二、排序合并連接(Sort Merge)排序合并連接的方法非常簡單。在排序合并連接中是沒有驅(qū)動表的概念的， 兩個 互相連接的表按連接列的值先排序，排序完后形成的結(jié)果集再互相進(jìn)行合并連接 提取符合條件的記錄。相比嵌套循環(huán)連接，排序合并連接比較適用于返回大數(shù)據(jù) 量的結(jié)果。以下為排序合并連接的例子：RobyXUESelect emp.e name,dept.d name2 from

10、 emp,dept3 where emp.dept no=dept.dept no4 /ENAME DNAMECLARK ACCOUNTINGKING ACCOUNTINGMILLER ACCOUNTINGJONES RESEARCHSCOTT RESEARCHFORD RESEARCHADAMS RESEARCHTURNER SALES JAMES SALESWARD SALES MARTIN SALESBLAKE SALES12 rows selected.Executio n Pla n| Id | Operatio n | Name | Rows | Bytes | Cost (%CP

11、U)| 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

12、| 108 | 3 (0)| 00:00:01 |可以看得出來上述查詢首先按dept、emp兩張表的dept no先排序，然后排序好 的結(jié)果集再進(jìn)行合并連接返回最終的記錄。排序合并連接在數(shù)據(jù)表預(yù)先排序好的情況下效率是非常高的，也比較適用于非等值連接的情況，比如 、=、=等情況下的連接（哈希連接只適用于等值連接）。由于Oracle中排序操作的開銷是非常消耗資源的，當(dāng)結(jié)果集很大時排序合并連 接的性能很差，于是Oracle在7.3之后推出了新的連接方式一一哈希連接。三、哈希連接(Hash join )哈希連接分為兩個階段，如下。1、構(gòu)建階段：優(yōu)化器首先選擇一張小表做為驅(qū)動表，運(yùn)用哈希函數(shù)對連接列進(jìn)

13、行計算產(chǎn)生一張哈希表。通常這個步驟是在內(nèi)存(hash_area_size )里面進(jìn)行的， 因此運(yùn)算很快。2、探測階段：優(yōu)化器對被驅(qū)動表的連接列運(yùn)用同樣的哈希函數(shù)計算得到的結(jié)果與前面形成的哈希表進(jìn)行探測返回符合條件的記錄。這個階段中如果被驅(qū)動表的連接列的值沒有與驅(qū)動表連接列的值相等的話，那么這些記錄將會被丟棄而不進(jìn)行探測。關(guān)于哈希連接更深層次的原理可以參考Itpub上網(wǎng)友發(fā)表的“ hash join算法原理”帖子()。以下為哈希連接的一個例子：RobyXUEselect /*/*+ use_hash(emp,dept) */ emp.e name,dept.d name2 from emp,de

14、pt3 where emp.dept no=dept.dept no;ENAMEDNAMEWARDSALESJONESRESEARCHMARTINSALESBLAKESALESCLARKACCOUNTINGSCOTTRESEARCHKINGACCOUNTINGTURNERSALESADAMSRESEARCHJAMESSALESFORDRESEARCHMILLERACCOUNTING12 rows selected.Executi on Pla n| Id| Operati on(%CPU)| Time| Name | Rows| Bytes | Cost|0 | SELECT STATEMEN

15、T |7(15)| 00:00:01 |112 |2641* 1 11HASH JOIN7(15)| 00:00:01 |1112 |2641 2 |13TABLE ACCESS FULL| DEPT |(0)| 00:00:01 |4 |521*3 |TABLE ACCESS FULL| EMP |12 |108|3(0)| 00:00:01 |在這個查詢中優(yōu)化器首先選擇 dept這張表為驅(qū)動表，對列dept no運(yùn)算哈希函數(shù) 構(gòu)建一張哈希表，然后再對被驅(qū)動表emp的dept no列運(yùn)算同樣的哈希函數(shù)計算得到的結(jié)果進(jìn)行探測，最終連接得出符合條件的記錄。同嵌套循環(huán)外連接一樣，哈希循環(huán)外連接的驅(qū)

16、動表同樣是沒有符合條件關(guān)聯(lián)的那 張表。如下述例子：RobyXUESelect /*/*+ use_hash(emp,dept) */emp.e name,dept.d name2 from emp,dept3 where emp.dept no=dept.dept no (+);ENAME DNAMEMILLER ACCOUNTING KING ACCOUNTING CLARK ACCOUNTING FORD RESEARCH ADAMS RESEARCH SCOTT RESEARCH JONES RESEARCH JAMES SALESTURNER SALESBLAKE SALESMARTI

17、N SALESWARD SALESALLENSMITH14 rows selected.Executio n Pla n| Id | Operatio n | 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模式下(show parameter optimizer_mode) ，參數(shù) hashoin_enabled 設(shè)置為 true，且只適用于等值連接。從 Oracle9i開始，哈希連接由于其良好的性能漸 漸取代了原來的排序合并連接。四、跟表連接有關(guān)的幾