本文討論一下join技術(shù)背后的機(jī)制。我們知道常用的表連接有如下幾種

網(wǎng)站建設(shè)哪家好，找創(chuàng)新互聯(lián)！專注于網(wǎng)頁設(shè)計(jì)、網(wǎng)站建設(shè)、微信開發(fā)、小程序定制開發(fā)、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了秀嶼免費(fèi)建站歡迎大家使用！

• 笛卡爾連接

• 內(nèi)連接

• 左外連接

• 右外連接

• 全連接

這些sql的寫法，想必大家都很清楚了，那么這些連接的數(shù)據(jù)訪問是如何實(shí)現(xiàn)的呢？

• nested loop

我們看如下查詢

SQL> alter session set optimizer_mode=rule; Session altered. SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3625962092 ------------------------------------------------ | Id  | Operation      | Name    | ------------------------------------------------ |   0 | SELECT STATEMENT      |        | |   1 |  NESTED LOOPS      |        | |   2 |   NESTED LOOPS      |        | |   3 |    TABLE ACCESS FULL      | EMP     | |*  4 |    INDEX UNIQUE SCAN      | PK_DEPT | |   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    | ------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

根據(jù)我們之前講的執(zhí)行計(jì)劃解讀，本查詢是這樣實(shí)現(xiàn)的：

1. 全表掃描emp表（非阻塞掃描，并不是將數(shù)據(jù)全部取出，才執(zhí)行下一步）。

2. 將emp中的數(shù)據(jù)逐條取出，通過索引PK_DEPT查詢出索引中的rowid，結(jié)果集變成（ename，rowid）

3. 將2生成的結(jié)果集逐條取出，通過rowid去訪問dept表，結(jié)果集變成（ename，dname）

4. 將結(jié)果集返回。

這種以循環(huán)的方式取出數(shù)據(jù)的join實(shí)現(xiàn)方式就叫嵌套循環(huán)。

此計(jì)劃可以用如下邏輯偽代碼實(shí)現(xiàn)

for y in （for x in （select * from emp）loop          index lookup the rowid for x.deptno          output joined record（ename，dept.rowid）        end loop）loop  select * from dept where rowid=y.rowid  output joined record(ename,dname) end loop

我們把emp表稱之為驅(qū)動(dòng)表（注驅(qū)動(dòng)表與from子句的表順序無關(guān)，主要看執(zhí)行計(jì)劃）。

此種連接方式，適用于驅(qū)動(dòng)表返回?cái)?shù)據(jù)比較少，并且被驅(qū)動(dòng)表dept上deptno列有索引。如果查詢返回n行，那么dept表將被掃描n次。此連接擅長于從結(jié)果集中迅速取出第一行。

• Hash Join

 Hash Join適合處理大型結(jié)果集，優(yōu)化器選擇兩個(gè)表或者源數(shù)據(jù)中比較小的，使用join key在內(nèi)存中建立一個(gè)hash table。然后掃描大表，并探查hash表，去發(fā)現(xiàn)匹配的記錄。
 小表稱為驅(qū)動(dòng)表，大表稱為探查表

當(dāng)hash table能全部放到內(nèi)存中，此種情況最好。如果內(nèi)存中放不下hash table，優(yōu)化器將hash table分區(qū)，超出內(nèi)存范圍的分區(qū)將被寫到臨時(shí)表空間中。

我們分兩種情況討論hash join的實(shí)現(xiàn)

1. hash table 全部在內(nèi)存里

 hash table是Oracle根據(jù)join key利用一個(gè)hash函數(shù)將小表分割成多個(gè)bucket。hash table建立完成后，Oracle去掃描大表，并且采用相同的hash算法，將讀入的數(shù)據(jù)也分割成多個(gè)bucket。bucket與bucket之間進(jìn)行join運(yùn)算，返回結(jié)果。直到大表讀完為止。

2. hash table 不能全部放到內(nèi)存中

 這種情況有點(diǎn)麻煩，當(dāng)Oracle發(fā)現(xiàn)內(nèi)存無法完全存放小表，Oracle在構(gòu)造hash table時(shí)，將小表進(jìn)行分區(qū)，每個(gè)分區(qū)中再構(gòu)造bucket 。當(dāng)內(nèi)存寫滿時(shí)，Oracle將內(nèi)存中大的分區(qū)寫到tempfile中。用這個(gè)方法，直到小表hash table構(gòu)造完成。此時(shí)，hashtable一部分?jǐn)?shù)據(jù)在內(nèi)存，一部分?jǐn)?shù)據(jù)在tempfile。

 當(dāng)Oracle去掃描大表時(shí)，如果掃描的行通過hash在內(nèi)存中能找到結(jié)果，就匹配成功。如果不能命中，則采用與hash table相同的分區(qū)方式，先將數(shù)據(jù)寫入tempfile中。當(dāng)大表全部掃描完畢，hash table內(nèi)存中的部分已全部匹配完。此時(shí)依次將tempfile中的分區(qū)加載到內(nèi)存中。重新掃描大表臨時(shí)存在tempfile中的相應(yīng)分區(qū)加以匹配。直到數(shù)據(jù)全部處理完。

SQL> insert into big_emp select * from big_emp; SQL> insert into big_emp select * from big_emp;#重復(fù)執(zhí)行多次 SQL> / 458752 rows created. SQL> create table dept_new as select * from dept; Table created. SQL> set autot traceonly SQL> select * from big_emp a,dept_new b where a.deptno=b.deptno; 917504 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1925493178 ------------------------------------------------------------------------------- | Id  | Operation    | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |       |   917K|    54M|  1490 (2)| 00:00:18 | |*  1 |  HASH JOIN    |       |   917K|    54M|  1490 (2)| 00:00:18 | |   2 |   TABLE ACCESS FULL| DEPT_NEW |     4 |   120 |     3 (0)| 00:00:01 | |   3 |   TABLE ACCESS FULL| BIG_EMP  |   917K|    28M|  1482 (1)| 00:00:18 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - access("A"."DEPTNO"="B"."DEPTNO") Note -----    - dynamic sampling used for this statement (level=2) Statistics ----------------------------------------------------------   4  recursive calls   1  db block gets       66338  consistent gets   0  physical reads   0  redo size    62512398  bytes sent via SQL*Net to client      673349  bytes received via SQL*Net from client       61168  SQL*Net roundtrips to/from client   0  sorts (memory)   0  sorts (disk)      917504  rows processed

• Sort Merge Joins

  排序合并連接與嵌套循環(huán)和散列連接都不同，排序合并連接沒有驅(qū)動(dòng)表的概念。簡言之，排序合并將依次處理排序第一個(gè)輸入集，排序第二個(gè)輸入集，然后合并結(jié)果。排序合并通常不如散列高效，因?yàn)閮蓚€(gè)結(jié)果集都需要排序，而散列連接在數(shù)據(jù)輸出前，只需處理一個(gè)結(jié)果集。排序合并通常在非等值連接中有效。即連接條件不是一個(gè)等式而是范圍比較（<或者>=）. 或者是兩個(gè)表的數(shù)據(jù)已經(jīng)排好序啦。

我們看如下例子

SQL> set linesize 200 pagesize 200 SQL> set autot traceonly SQL> select a.ename,b.ename,a.hiredate,b.hiredate   2  from emp a,emp b   3  where a.empno<>b.empno and a.hiredate<b.hiredate; 90 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3733349388 ----------------------------------------------------------------------------- | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |     |  84 |  3024 |   8  (25)| 00:00:01 | |   1 |  MERGE JOIN      |     |  84 |  3024 |   8  (25)| 00:00:01 | |   2 |   SORT JOIN      |     |  14 | 252 |   4  (25)| 00:00:01 | |   3 |    TABLE ACCESS FULL | EMP  |  14 | 252 |   3   (0)| 00:00:01 | |*  4 |   FILTER      |     |     |     |  |     | |*  5 |    SORT JOIN      |     |  14 | 252 |   4  (25)| 00:00:01 | |   6 |     TABLE ACCESS FULL| EMP  |  14 | 252 |   3   (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    4 - filter("A"."EMPNO"<>"B"."EMPNO")    5 - access("A"."HIREDATE"<"B"."HIREDATE")        filter("A"."HIREDATE"<"B"."HIREDATE") Statistics ----------------------------------------------------------   1  recursive calls   0  db block gets  12  consistent gets   0  physical reads   0  redo size        3500  bytes sent via SQL*Net to client 578  bytes received via SQL*Net from client   7  SQL*Net roundtrips to/from client   2  sorts (memory)   0  sorts (disk)  90  rows processed

再看一個(gè)等值連接的

SQL> select ename,dname from emp a,dept b where a.deptno=b.deptno; 14 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |        |    14 |   308 |     6 (17)| 00:00:01 | |   1 |  MERGE JOIN      |        |    14 |   308 |     6 (17)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2  (0)| 00:00:01 | |   3 |    INDEX FULL SCAN      | PK_DEPT |     4 |       |     1  (0)| 00:00:01 | |*  4 |   SORT JOIN      |        |    14 |   126 |     4 (25)| 00:00:01 | |   5 |    TABLE ACCESS FULL      | EMP     |    14 |   126 |     3  (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    4 - access("A"."DEPTNO"="B"."DEPTNO")        filter("A"."DEPTNO"="B"."DEPTNO") Statistics ----------------------------------------------------------   1  recursive calls   0  db block gets  10  consistent gets   0  physical reads   0  redo size 819  bytes sent via SQL*Net to client 523  bytes received via SQL*Net from client   2  SQL*Net roundtrips to/from client   1  sorts (memory)   0  sorts (disk)  14  rows processed

另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。

網(wǎng)頁標(biāo)題：Oracle學(xué)習(xí)之性能優(yōu)化（七）join的實(shí)現(xiàn)方式-創(chuàng)新互聯(lián)
分享路徑：http://jinyejixie.com/article24/dehice.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站改版、營銷型網(wǎng)站建設(shè)、ChatGPT、品牌網(wǎng)站建設(shè)、電子商務(wù)、網(wǎng)站維護(hù)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)