Oracle學習之性能優化（七）join的實現方式-創新互聯
本文討論一下join技術背后的機制。我們知道常用的表連接有如下幾種
笛卡爾連接
內連接
左外連接
右外連接
全連接
這些sql的寫法，想必大家都很清楚了，那么這些連接的數據訪問是如何實現的呢？
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")根據我們之前講的執行計劃解讀，本查詢是這樣實現的：
全表掃描emp表（非阻塞掃描，并不是將數據全部取出，才執行下一步）。
將emp中的數據逐條取出，通過索引PK_DEPT查詢出索引中的rowid，結果集變成（ename，rowid）
將2生成的結果集逐條取出，通過rowid去訪問dept表，結果集變成（ename，dname）
將結果集返回。
這種以循環的方式取出數據的join實現方式就叫嵌套循環。
此計劃可以用如下邏輯偽代碼實現
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表稱之為驅動表（注驅動表與from子句的表順序無關，主要看執行計劃）。
此種連接方式，適用于驅動表返回數據比較少，并且被驅動表dept上deptno列有索引。如果查詢返回n行，那么dept表將被掃描n次。此連接擅長于從結果集中迅速取出第一行。
Hash Join
Hash Join適合處理大型結果集，優化器選擇兩個表或者源數據中比較小的，使用join key在內存中建立一個hash table。然后掃描大表，并探查hash表，去發現匹配的記錄。
小表稱為驅動表，大表稱為探查表
當hash table能全部放到內存中，此種情況最好。如果內存中放不下hash table，優化器將hash table分區，超出內存范圍的分區將被寫到臨時表空間中。
我們分兩種情況討論hash join的實現
hash table 全部在內存里
hash table是Oracle根據join key利用一個hash函數將小表分割成多個bucket。hash table建立完成后，Oracle去掃描大表，并且采用相同的hash算法，將讀入的數據也分割成多個bucket。bucket與bucket之間進行join運算，返回結果。直到大表讀完為止。
2. hash table 不能全部放到內存中
這種情況有點麻煩，當Oracle發現內存無法完全存放小表，Oracle在構造hash table時，將小表進行分區，每個分區中再構造bucket 。當內存寫滿時，Oracle將內存中大的分區寫到tempfile中。用這個方法，直到小表hash table構造完成。此時，hashtable一部分數據在內存，一部分數據在tempfile。
當Oracle去掃描大表時，如果掃描的行通過hash在內存中能找到結果，就匹配成功。如果不能命中，則采用與hash table相同的分區方式，先將數據寫入tempfile中。當大表全部掃描完畢，hash table內存中的部分已全部匹配完。此時依次將tempfile中的分區加載到內存中。重新掃描大表臨時存在tempfile中的相應分區加以匹配。直到數據全部處理完。
SQL> insert into big_emp select * from big_emp; SQL> insert into big_emp select * from big_emp;#重復執行多次 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 processedSort Merge Joins
排序合并連接與嵌套循環和散列連接都不同，排序合并連接沒有驅動表的概念。簡言之，排序合并將依次處理排序第一個輸入集，排序第二個輸入集，然后合并結果。排序合并通常不如散列高效，因為兩個結果集都需要排序，而散列連接在數據輸出前，只需處理一個結果集。排序合并通常在非等值連接中有效。即連接條件不是一個等式而是范圍比較（<或者>=）. 或者是兩個表的數據已經排好序啦。
我們看如下例子
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再看一個等值連接的
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另外有需要云服務器可以了解下創新互聯cdcxhl.cn，海內外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業上云的綜合解決方案，具有“安全穩定、簡單易用、服務可用性高、性價比高”等特點與優勢，專為企業上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。
網頁標題：Oracle學習之性能優化（七）join的實現方式-創新互聯
分享路徑：http://www.js-pz168.com/article24/dehice.html
成都網站建設公司_創新互聯，為您提供網站改版、營銷型網站建設、ChatGPT、品牌網站建設、電子商務、網站維護
聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯
- 火爆的ChatGPT，來聊聊它的熱門話題 2023-02-20
- ChatGPT是什么?ChatGPT是聊天機器人嗎? 2023-05-05
- 怎樣利用chatGPT快速賺錢？ 2023-05-05
- 馬云回國，首談ChatGPT。又是新一個風口？ 2023-05-28
- 爆紅的ChatGPT，誰會丟掉飯碗？ 2023-02-20
- ChatGPT的發展歷程 2023-02-20
- ChatGPT是什么 2023-02-20
- ChatGPT的應用ChatGPT對社會的利弊影響 2023-02-20