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
Final number of hash buckets: Hash Bucket數量
Total buckets:  Empty  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>

【相關推薦：SQL教學】

以上是Oracle表格連線方式的最佳化方法（附範例）的詳細內容。更多資訊請關注PHP中文網其他相關文章！