有哪些Oracle表連接方式的優化方法

有哪些Oracle表連接方式的優化方法？針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

在Oracle數據庫中，兩個表之間的表連接方法有排序合并連接、嵌套循環連接、哈希連接和笛卡爾連接四種

1.排序合并連接（sort merge join）

排序合并連接是一種兩表在做表連接時用排序（SORT）操作和合并（MERGE）操作來得到連接結果集的表連接方法

如果t1表和t2表在做表連接時使用的是排序合并連接，那么Oracle會依次執行如下步驟：

a.以目標SQL中指定的謂詞條件訪問t1表，然后對訪問結果按照t1表的連接列排序，排好序后的結果集記為s1

b.以目標SQL中指定的謂詞條件訪問t2表，然后對訪問結果按照t2表的連接列排序，排好序后的結果集記為s2

c.對s1和s2進行合并操作，從中取出匹配記錄作為最終的結果集

排序合并連接的優缺點及適用場景：

a.通常情況下hash join的效果都比sort merge join要好，但是，如果行源已經被排過序，在執行sort merge join時不需要再排序，這時sort merge join的性能會優于hash join

b.通常情況下，只有在以下情況發生時，才會使用排序合并連接：

1)RBO模式

2)不等值連接（>,<,>=,<=）

3)哈希連接被禁用時（_HASH_JOIN_ENABLED=false）

示例

SQL> select * from scott.emp t1,scott.emp t2 where t1.empno > t2.mgr;

89 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3950110903

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    62 |  4712 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                   |        |    62 |  4712 |     6  (17)| 00:00:01 |
|   2 |   SORT JOIN                   |        |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  5 |   SORT JOIN                   |        |    14 |   532 |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL          | EMP    |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))
       filter(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       6612  bytes sent via SQL*Net to client
        575  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         89  rows processed

SQL>

2.嵌套循環連接（nested loops join）

嵌套循環連接是一種兩表在做表連接時依靠兩層嵌套循環（外層循環/內層循環）來得到連接結果集的表連接方法

如果t1表和t2表在做表連接時使用的是嵌套循環連接，那么Oracle會依次執行如下步驟：

a.首先，優化器會按照一定的規則來決定t1和t2誰是驅動表誰是被驅動表，驅動表用于外層循環，被驅動表用于內存循環。假設t1是驅動表

b.以目標SQL中指定的謂詞條件訪問驅動表t1，得到結果集s1

c.遍歷s1，同時遍歷被驅動表t2，即取出s1中的記錄按照連接條件和被驅動表t2做匹配。最終將得到的結果集返回

嵌套循環連接的優缺點及適用場景：

a.能夠實現快速響應，即可以第一時間先返回已經連接過且滿足連接條件的記錄，而不必等待所有的連接操作全部做完后才返回連接結果

b.適用于驅動表所對應的驅動結果集的記錄數較少，同時在被驅動表的連接列上又存在唯一性索引（或者在被驅動表的連接列上存在選擇性很好的非唯一性索引）的情況

示例

SQL> select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dcsf9m1rzzga5, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno

Plan hash value: 4192419542

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      32 |
|   1 |  NESTED LOOPS      |      |      1 |     14 |     14 |00:00:00.01 |      32 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      4 |     14 |00:00:00.01 |      25 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
- filter("T1"."DEPTNO"="T2"."DEPTNO")
rows selected.

SQL>

3.哈希連接（hash join）

哈希連接是一種兩表在做表連接時依靠哈希運算來得到連接結果集的表連接方法，oracle 7.3之后引入

Hash join的工作方式是將一個表（通常是小一點的那個表）做hash運算并存儲到hash列表中，從另一個表中抽取記錄，做hash運算，到hash 列表中找到相應的值，做匹配

哈希連接只適用于CBO，也只能用于等值連接條件

哈希連接很適合于小表和大表做連接，特別是在小表的連接列的可選擇性非常好的情況下，這時候哈希連接的執行時間就可以近似看作是和全表掃描那個大表所耗費的時間相當

哈希連接時，驅動結果集對應的Hash Table能夠完全被容納在內存中（PGA的工作區），此時的哈希連接的執行效率非常高

哈希連接的性能問題可以通過10104事件來診斷，相關說明如下：　　　

Number of in-memory partitions (may have changed): Hash Partition
Final number of hash buckets: Hash Bucket數量
Total buckets:  Empty buckets:  Non-empty buckets: Hash Bucket中空記錄及非空記錄的情況
Total number of rows: 驅動結果集的記錄數
Maximum number of rows in a bucket: 包含記錄數最多的Hash Bucket所含記錄的數量
Disabled bitmap filtering: 是否啟用位圖過濾

示例

SQL> select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0j83q86ara5u2, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno

Plan hash value: 615168685

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     14 |     14 |00:00:00.01 |      13 |  1321K|  1321K| 1070K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")


21 rows selected.

SQL>

關于有哪些Oracle表連接方式的優化方法問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。