Oracle Table連接方式分析

Oracle Table連接方式分析

表連接基本知識：

1、哪張表將驅動查詢（即訪問的第一張表）？按照指定的路徑查詢，何時將訪問到沒一張表？可選的驅動路徑有哪些？

2、可能出現哪些Oracle連接？記住：在Oracle中，連接順序、可選的索引、用于排序和建立散列表的可用內存的不同都會導致不同的結果。

3、哪些索引是可用的?哪些索引是可選的？索引的選擇不僅僅將導致優化器使用或者限制一個索引，還將改變驅動查詢的方式，并可能決定使用或者限制查詢中其他的索引。

4、哪些提示提供了可選的路徑？哪些提示限制或強制使用索引？這些提示不僅僅改變了表的驅動順序，還改變了Oracle中連接的性能，并可決定限制或強制使用哪些索引。

5、您在使用哪一個版本的Oracle？你的選擇取決于你使用的Oracle的版本。不同的版本優化器的工作方式也不一樣。

表連接方式：

在查看sql執行計劃時，我們會發現表的連接方式有多種，本文對表的連接方式進行介紹以便更好看懂執行計劃和理解sql執行原理。 

一、連接方式：

       嵌套循環（Nested  Loops （NL））

      （散列）哈希連接（Hash Join （HJ））

       排序合并連接(Sort Merge Join (SMJ) )

二、連接說明：

    1.Oracle一次只能連接兩個表。不管查詢中有多少個表，Oracle 在連接中一次僅能操作兩張表。

    2.當執行多個表的連接時，優化器從一個表開始，將它與另一個表連接；然后將中間結果與下一個表連接，以此類推，直到處理完所有表為止。

ORACLE從6的版本開始，優化器使用4種不同的表的連接方式：

嵌套循環連接（NESTEDLOOPJOIN）

群集連接（CLUSTERJOIN）

排序合并連接（SORTMERGEJOIN）

笛卡爾連接（CARTESIANJOIN）

哈希連接（HASHJOIN）

索引連接（INDEXJOIN）

　　這六種連接方式都有其獨特的技術特點，在一定的條件下，可以充分發揮高效的性能。

　　但是也都有其局限性，如果使用不當，不僅不能提高效率，反而會嚴重影響系統的性能。因此，深入地探討連接方式的內部運行機制對于性能優化是必要的。

　　1、嵌套循環連接

　　嵌套循環連接的內部處理的流程：

　　1）Oracle優化器根據基于規則RBO（rulebasedoptimizer）或基于成本CBO（costbasedoptimizer）的原則，選擇兩個表中的一個作為驅動表，并指定其為外部表。

　　2）Oracle優化器再將另外一個表指定為內部表。

　　3）Oracle從外部表中讀取第一行,然后和內部表中的數據逐一進行對比，所有匹配的記錄放在結果集中。

　　4）Oracle讀取外部表中的第二行，再和內部表中的數據逐一進行對比，所有匹配的記錄添加到結果集中。

　　5）重復上述步驟，直到外部表中的所有紀錄全部處理完。

　　6）最后產生滿足要求的結果集。

    使用嵌套循環連接是一種從結果集中提取第一批記錄最快速的方法。在驅動行源表（就是正在查找的記錄）較小、或者內部行源表已連接的列有惟一的索引或高度可選的非惟一索引時，嵌套循環連接效果是比較理想的。嵌套循環連接比其他連接方法有優勢，它可以快速地從結果集中提取第一批記錄，而不用等待整個結果集完全確定下來。這樣，在理想情況下，終端用戶就可以通過查詢屏幕查看第一批記錄，而在同時讀取其他記錄。不管如何定義連接的條件或者模式，任何兩行記錄源可以使用嵌套循環連接，所以嵌套循環連接是非常靈活的。

　　然而，如果內部行源表（讀取的第二張表）已連接的列上不包含索引，或者索引不是高度可選時，嵌套循環連接效率是很低的。如果驅動表的記錄非常龐大時，其他的連接方法可能更加有效。

　　可以通過在SQL語句中添加HINTS,強制ORACLE優化器產生嵌套循環連接的執行計劃。

　　select/*+use_nl(ab)*/a.user_name,b.dev_nofromuser_infoa,dev_infobwherea.user_id=b.user_id;

案例：

12:00:44 SCOTT@ prod> select  e.ename,e.sal,d.deptno,d.dname 
12:01:50   2    from emp e,dept d
12:01:50   3    where d.deptno=e.deptno and d.deptno=10 ;
ENAME             SAL     DEPTNO DNAME
---------- ---------- ---------- --------------
CLARK            2450         10 ACCOUNTING
KING             5000         10 ACCOUNTING
MILLER           1300         10 ACCOUNTING
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 568005898
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   105 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     5 |   105 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMP     |     5 |    50 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("D"."DEPTNO"=10)
   4 - filter("E"."DEPTNO"=10)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        835  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
12:01:51 SCOTT@ prod>
11:59:48 SCOTT@ prod>select /*+ use_nl(d e)*/ e.ename,e.sal,d.deptno,d.dname 
11:59:49   2    from dept d,emp e
11:59:49   3    where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   294 |    10   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |      |    14 |   294 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    44 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     4 |    40 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         37  consistent gets
          0  physical reads
          0  redo size
       1038  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

排序合并連接（SORTMERGEJOIN）

　　排序合并連接內部處理的流程：

　　1）優化器判斷第一個源表是否已經排序，如果已經排序，則到第3步，否則到第2步。

　　2）第一個源表排序

　　3）優化器判斷第二個源表是否已經排序，如果已經排序，則到第5步，否則到第4步。

　　4）第二個源表排序

　　5）已經排過序的兩個源表進行合并操作，并生成最終的結果集。

　　在缺乏數據的選擇性或者可用的索引時，或者兩個源表都過于龐大（所選的數據超過表記錄數的5%）時，排序合并連接將比嵌套循環連更加高效。

　　排列合并連接需要比較大的臨時內存塊，以用于排序，這將導致在臨時表空間占用更多的內存和磁盤I/O。

可以通過在SQL語句中添加HINTS,強制ORACLE優化器產生排序合并連接的執行計劃。

　　select/*+use_merge(ab)*/a.user_name,b.dev_nofromuser_infoa,dev_infobwherea.user_id>b.user_id;

案例：

11:57:04 SCOTT@ prod> select  e.ename,e.sal,d.deptno,d.dname 
  from dept d,emp e
11:57:13   2  11:57:13   3    where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   294 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   294 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   140 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   140 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1042  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
11:57:14 SCOTT@ prod>select /*+ order */ e.ename,e.sal,d.deptno,d.dname 
11:58:14   2    from dept d,emp e
11:58:14   3    where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   294 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   294 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    44 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   140 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   140 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("D"."DEPTNO"="E"."DEPTNO")
       filter("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1042  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
11:58:23 SCOTT@ prod>
11:59:48 SCOTT@ prod>

哈希連接

　　當內存能夠提供足夠的空間時，哈希（HASH）連接是Oracle優化器通常的選擇。哈希連接中，優化器根據統計信息，首先選擇兩個表中的小表，在內存中建立這張表的基于連接鍵的哈希表；優化器再掃描表連接中的大表，將大表中的數據與哈希表進行比較，如果有相關聯的數據，則將數據添加到結果集中。

　　當表連接中的小表能夠完全cache到可用內存的時候，哈希連接的效果最佳。哈希連接的成本只是兩個表從硬盤讀入到內存的成本。

　　但是，如果哈希表過大而不能全部cache到可用內存時，優化器將會把哈希表分成多個分區，再將分區逐一cache到內存中。當表的分區超過了可用內存時，分區的部分數據就會臨時地寫到磁盤上的臨時表空間上。因此，分區的數據寫磁盤時，比較大的區間（EXTENT）會提高I/O性能。ORACLE推薦的臨時表空間的區間是1MB。臨時表空間的區間大小由UNIFORMSIZE指定。

　　當哈希表構建完成后，進行下面的處理：

　　1）第二個大表進行掃描

　　2）如果大表不能完全cache到可用內存的時候，大表同樣會分成很多分區

　　3）大表的第一個分區cache到內存

　　4）對大表第一個分區的數據進行掃描，并與哈希表進行比較，如果有匹配的紀錄，添加到結果集里面5）與第一個分區一樣，其它的分區也類似處理。

　　6）所有的分區處理完后，ORACLE對產生的結果集進行歸并，匯總，產生最終的結果。

　　當哈希表過大或可用內存有限，哈希表不能完全CACHE到內存。隨著滿足連接條件的結果集的增加，可用內存會隨之下降，這時已經CACHE到內存的數據可能會重新寫回到硬盤去。如果出現這種情況，系統的性能就會下降。

　　當連接的兩個表是用等值連接并且表的數據量比較大時，優化器才可能采用哈希連接。哈希連接是基于CBO的。只有在數據庫初始化參數HASH_JOIN_ENABLED設為True,并且為參數PGA_AGGREGATE_TARGET設置了一個足夠大的值的時候,Oracle才會使用哈希連接。HASH_AREA_SIZE是向下兼容的參數,但在Oracle9i之前的版本中應當使用HASH_AREA_SIZE。當使用ORDERED提示時,FROM子句中的第一張表將用于建立哈希表。

　　可以通過在SQL語句中添加HINTS,強制ORACLE優化器產生哈希連接的執行計劃。

　　select/*+use_hash(ab)*/a.user_name,b.dev_nofromuser_infoa,dev_infobwherea.user_id=b.user_id;

　　當缺少有用的索引時，哈希連接比嵌套循環連接更加有效。哈希連接也可能比嵌套循環連接更快，因為處理內存中的哈希表比檢索B_樹索引更加迅速。

案例：

11:59:56 SCOTT@ prod> select /*+ use_hash(d e)*/ e.ename,e.sal,d.deptno,d.dname 
12:00:34   2    from dept d,emp e
12:00:34   3    where d.deptno=e.deptno ;
14 rows selected.
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   294 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   294 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    44 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("D"."DEPTNO"="E"."DEPTNO")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       1107  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

HASH JOIN與NETSTED LOOP、SORT MERGE JOIN連接方式對比：

介紹Hash Join算法步驟： 

1、Hash Join連接對象依然是兩個數據表，首先選擇出其中一個“小表”。這里的小表，就是參與連接操作的數據集合數據量小。對連接列字段的所有數據值，進行Hash函數操作。Hash函數是計算機科學中經常使用到的一種處理函數，利用Hash值的快速搜索算法已經被認為是成熟的檢索手段。Hash函數處理過的數據特征是“相同數據值的Hash函數值一定相同，不同數據值的Hash函數值可能相同”；

2、經過Hash處理過的小表連接列，連同數據一起存放到Oracle PGA空間中。PGA中存在一塊空間為hash_area，專門存放此類數據。并且，依據不同的Hash函數值，進行劃分Bucket操作。每個Bucket中包括所有相同hash函數值的小表數據。同時建立Hash鍵值對應位圖。

3、之后對進行Hash連接大表數據連接列依次讀取，并且將每個Hash值進行Bucket匹配，定位到適當的Bucket上（應用Hash檢索算法）；

4、在定位到的Bucket中，進行小規模的精確匹配。因為此時的范圍已經縮小，進行匹配的成功率精確度高。同時，匹配操作是在內存中進行，速度較Merge Sort Join時要快很多；

案例：

12:16:16 SYS@ prod>create table sgtb as select * from dba_segments where owner='SYS';
Table created.
Elapsed: 00:00:00.73
12:17:05 SYS@ prod>create table obtb as select * from dba_objects where owner='SYS';
Table created.
Elapsed: 00:00:01.02
12:17:30 SYS@ prod>SELECT count(*) from sgtb;
  COUNT(*)
----------
      2312
Elapsed: 00:00:00.02
12:17:41 SYS@ prod>SELECT count(*) from obtb;
  COUNT(*)
----------
     30928
Elapsed: 00:00:00.04
12:17:51 SYS@ prod>
12:17:51 SYS@ prod>create index seg_name_ind on sgtb (segment_name);
Index created.
Elapsed: 00:00:00.27
12:19:00 SYS@ prod>create index ob_name_ind on obtb(object_name);
Index created.
Elapsed: 00:00:00.32
12:19:29 SYS@ prod>
12:19:29 SYS@ prod>exec dbms_stats.gather_table_stats(user,'SGTB',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.46
12:20:49 SYS@ prod>exec dbms_stats.gather_table_stats(user,'OBTB',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.33

HASH JOIN:

12:21:03 SYS@ prod>SET autotrace trace
12:21:32 SYS@ prod>select * from sgtb a,obtb b where a.segment_name=b.object_name;
2528 rows selected.
Elapsed: 00:00:00.14
Execution Plan
----------------------------------------------------------
Plan hash value: 1028776806
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2401 |   515K|   134   (1)| 00:00:02 |
|*  1 |  HASH JOIN         |      |  2401 |   515K|   134   (1)| 00:00:02 |
|   2 |   TABLE ACCESS FULL| SGTB |  2312 |   279K|    13   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| OBTB | 30928 |  2899K|   121   (1)| 00:00:02 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        650  consistent gets
          0  physical reads
          0  redo size
     223156  bytes sent via SQL*Net to client
       2371  bytes received via SQL*Net from client
        170  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2528  rows processed

NETSTED LOOP:

12:22:41 SYS@ prod>select /*+ use_nl(a b) */ * from sgtb a,obtb b where a.segment_name=b.object_name;
2528 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 2080873268
--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |  2401 |   515K|  4638   (1)| 00:00:56 |
|   1 |  NESTED LOOPS                |             |       |       |            |          |
|   2 |   NESTED LOOPS               |             |  2401 |   515K|  4638   (1)| 00:00:56 |
|   3 |    TABLE ACCESS FULL         | SGTB        |  2312 |   279K|    13   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | OB_NAME_IND |     1 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| OBTB        |     1 |    96 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3065  consistent gets
          0  physical reads
          0  redo size
     213135  bytes sent via SQL*Net to client
       2371  bytes received via SQL*Net from client
        170  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2528  rows processed

SORT MERGE JOIN:

12:24:30 SYS@ prod>select /*+ use_merge(a b) */ * from sgtb a,obtb b where a.segment_name=b.object_name;
2528 rows selected.
Elapsed: 00:00:00.16
Execution Plan
----------------------------------------------------------
Plan hash value: 2191280214
------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  2401 |   515K|       |   883   (1)| 00:00:11 |
|   1 |  MERGE JOIN         |      |  2401 |   515K|       |   883   (1)| 00:00:11 |
|   2 |   SORT JOIN         |      |  2312 |   279K|   840K|    80   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| SGTB |  2312 |   279K|       |    13   (0)| 00:00:01 |
|*  4 |   SORT JOIN         |      | 30928 |  2899K|  8136K|   803   (1)| 00:00:10 |
|   5 |    TABLE ACCESS FULL| OBTB | 30928 |  2899K|       |   121   (1)| 00:00:02 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
       filter("A"."SEGMENT_NAME"="B"."OBJECT_NAME")
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        485  consistent gets
          0  physical reads
          0  redo size
     235884  bytes sent via SQL*Net to client
       2371  bytes received via SQL*Net from client
        170  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       2528  rows processed

成本對比：

       最后，Hash Join使用的場景是有限制的。其中最大的一個就是連接操作僅能使用“=”連接。因為Hash匹配的過程只能支持相等操作。還有就是連接列的數據分布要盡量做到數據分布均勻，這樣產生的Bucket也會盡可能均勻。這樣限制匹配的速度才有保證。如果數據列分布偏移嚴重，Hash Join算法效率會有退化趨勢。

---以上內容整理自書籍和網絡，感謝感謝各位作者！