有哪些Oracle表連接方式的優(yōu)化方法

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

在Oracle數(shù)據(jù)庫中，兩個(gè)表之間的表連接方法有排序合并連接、嵌套循環(huán)連接、哈希連接和笛卡爾連接四種

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

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

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

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

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

c.對(duì)s1和s2進(jìn)行合并操作，從中取出匹配記錄作為最終的結(jié)果集

排序合并連接的優(yōu)缺點(diǎn)及適用場景：

a.通常情況下hash join的效果都比sort merge join要好，但是，如果行源已經(jīng)被排過序，在執(zhí)行sort merge join時(shí)不需要再排序，這時(shí)sort merge join的性能會(huì)優(yōu)于hash join

b.通常情況下，只有在以下情況發(fā)生時(shí)，才會(huì)使用排序合并連接：

1)RBO模式

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

3)哈希連接被禁用時(shí)（_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.嵌套循環(huán)連接（nested loops join）

嵌套循環(huán)連接是一種兩表在做表連接時(shí)依靠兩層嵌套循環(huán)（外層循環(huán)/內(nèi)層循環(huán)）來得到連接結(jié)果集的表連接方法

如果t1表和t2表在做表連接時(shí)使用的是嵌套循環(huán)連接，那么Oracle會(huì)依次執(zhí)行如下步驟：

a.首先，優(yōu)化器會(huì)按照一定的規(guī)則來決定t1和t2誰是驅(qū)動(dòng)表誰是被驅(qū)動(dòng)表，驅(qū)動(dòng)表用于外層循環(huán)，被驅(qū)動(dòng)表用于內(nèi)存循環(huán)。假設(shè)t1是驅(qū)動(dòng)表

b.以目標(biāo)SQL中指定的謂詞條件訪問驅(qū)動(dòng)表t1，得到結(jié)果集s1

c.遍歷s1，同時(shí)遍歷被驅(qū)動(dòng)表t2，即取出s1中的記錄按照連接條件和被驅(qū)動(dòng)表t2做匹配。最終將得到的結(jié)果集返回

嵌套循環(huán)連接的優(yōu)缺點(diǎn)及適用場景：

a.能夠?qū)崿F(xiàn)快速響應(yīng)，即可以第一時(shí)間先返回已經(jīng)連接過且滿足連接條件的記錄，而不必等待所有的連接操作全部做完后才返回連接結(jié)果

b.適用于驅(qū)動(dòng)表所對(duì)應(yīng)的驅(qū)動(dòng)結(jié)果集的記錄數(shù)較少，同時(shí)在被驅(qū)動(dòng)表的連接列上又存在唯一性索引（或者在被驅(qū)動(dòng)表的連接列上存在選擇性很好的非唯一性索引）的情況

示例

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）

哈希連接是一種兩表在做表連接時(shí)依靠哈希運(yùn)算來得到連接結(jié)果集的表連接方法，oracle 7.3之后引入

Hash join的工作方式是將一個(gè)表（通常是小一點(diǎn)的那個(gè)表）做hash運(yùn)算并存儲(chǔ)到hash列表中，從另一個(gè)表中抽取記錄，做hash運(yùn)算，到hash 列表中找到相應(yīng)的值，做匹配

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

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

哈希連接時(shí)，驅(qū)動(dòng)結(jié)果集對(duì)應(yīng)的Hash Table能夠完全被容納在內(nèi)存中（PGA的工作區(qū)），此時(shí)的哈希連接的執(zhí)行效率非常高

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

Number of in-memory partitions (may have changed): Hash Partition
Final number of hash buckets: Hash Bucket數(shù)量
Total buckets:  Empty buckets:  Non-empty buckets: Hash Bucket中空記錄及非空記錄的情況
Total number of rows: 驅(qū)動(dòng)結(jié)果集的記錄數(shù)
Maximum number of rows in a bucket: 包含記錄數(shù)最多的Hash Bucket所含記錄的數(shù)量
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>

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