Oracle Table串連方式分析

Oracle Table串連方式分析

表串連基本知識：

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

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

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

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

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

--------------------------------------------------------------------------------

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

--------------------------------------------------------------------------------

表串連方式：

在查看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

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 3 4 下一頁