Oracle® Database SQL Tuning Guide

Part IV SQL Operators: Access Paths and Joins

--- 8 Optimizer Access Paths

--- 9 Joins

9 Joins

Oracle 資料庫為連線行集提供了幾種最佳化。

9.1 關於連線

連線將來自兩個行源（如表或檢視）的輸出合併起來，並返回一個行源。返回的行源是資料集。

連線的特徵是在WHERE （非 ANSI ）或 FROM 中多個表的. JOIN （ ANSI ）子語。當FROM 子句中存在多個表時， Oracle 資料庫將執行連接。

連接條件使用表示式比較兩個行源。連線條件定義表之間的關係。如果語句未指定連接條件，則資料庫執行笛卡爾連接 (Cartesian join) ，將一個表中的每一行與另一個表中的每一行匹配。

9.1.1 連線樹

通常，連接樹表示為倒樹結構。

如下圖所示，table1 為左表， table2 為右表。最佳化器從左到右處理連線。例如，如果這個圖形描述了一個巢狀迴圈連線 (nested loops join,) ，那麼table1 是外部迴圈 (outer loop) ，table2 是內部迴圈 (inner loop) 。

聯接的輸入可以是前一個聯接的結果集。如果聯接樹的每個內部節點的右子節點是一個表，那麼該樹就是一個左深聯接樹，如下面的示例所示。大多數連線樹都是左深連線。

如果聯接樹的每個內部節點的左子節點是一個表，則該樹稱為右深聯接樹，如下圖所示。

如果聯接樹的內部節點的左子節點或右子節點可以是聯接節點，則該樹稱為濃密聯接樹。在下面的示例中，table4 是一個聯接節點的右子節點， table1 是一個聯接節點的左子節點， table2 是一個聯接節點的左子節點。

在另一個變體中，聯接的兩個輸入都是前一個聯接的結果。

9.1.2 最佳化器如何執行連線語句

資料庫連線行源對。當FROM 子句中存在多個表時，最佳化器必須確定每對錶中哪一個聯接操作最有效。

最佳化器必須做出下表所示的相關決策。

Table 9-1 Join Operations

操作：訪問路徑

解釋：對於簡單語句，最佳化器必須選擇一個訪問路徑來從連線語句中的每個表檢索資料。例如，最佳化器可能會在全表掃描或索引掃描之間進行選擇。

操作：連線方式

解釋：要聯接每一對行源，Oracle 資料庫必須決定如何聯接。“ how ”是連線方法。可能的連線方法有巢狀迴圈 (nested loop) 、排序合併 (sort merge) 和雜湊連線 ( hash joins) 。笛卡爾連線 (Cartesian join) 需要前面的連線方法之一。每種連線方法都有特定的情況，在這些情況下，它比其他方法更適合。

操作：連線型別

解釋：聯接條件確定聯接型別。例如，內部聯接僅檢索與聯接條件匹配的行。外部聯接檢索與聯接條件不匹配的行。

操作：連線順序

解釋：要執行連線兩個以上表的語句，Oracle 資料庫先連線兩個表，然後將產生的行源連線到下一個表。這個過程將一直持續下去，直到所有表都連線到結果中為止。例如，資料庫連線兩個表，然後將結果連線到第三個表，然後將這個結果連線到第四個表，依此類推。

9.1.3 最佳化器如何為連線選擇執行計劃

在確定連線順序和方法時，最佳化器的目標是儘早減少行數，以便在整個SQL 語句執行過程中執行更少的工作。

最佳化器根據可能的連線順序、連線方法和可用的訪問路徑生成一組執行計劃。然後，最佳化器估計每個計劃的成本，並選擇成本最低的一個。在選擇執行計劃時，最佳化器會考慮以下因素:

• 最佳化器首先確定連線兩個或多個表是否會導致一個至多包含一行的行源。

最佳化器根據表上惟一的主鍵約束來識別這種情況。如果存在這種情況，那麼最佳化器將首先按照連線順序放置這些表。然後最佳化器最佳化其餘表集的連線。

• 對於帶有外部連線條件的連線語句，帶有外部連線運算子的表通常按照連線順序位於條件中的另一個表之後。

通常，最佳化器不會考慮違反此準則的連線順序，儘管在某些情況下，最佳化器會覆蓋此順序條件。類似地，當一個子查詢被轉換成反連線或半連線時，子查詢中的表必須位於它們所連線或關聯的外部查詢塊中的那些表之後。然而，雜湊反連線和半連線能夠在某些情況下覆蓋這個排序條件。

最佳化器透過計算估計的I/Os 和 CPU 來估計查詢計劃的成本。這些 I/Os 具有與之相關的特定成本 : 單個塊 I/O 的成本，以及多個塊 I/Os 的成本。另外，不同的函式和表示式都有與之相關的 CPU 成本。最佳化器使用這些指標確定查詢計劃的總成本。這些指標可能會受到許多初始化引數和編譯時的會話設定的影響，比如 DB_FILE_MULTI_BLOCK_READ_COUNT 設定、系統統計資訊等等。

例如，最佳化估計成本的方式如下:

• 巢狀迴圈聯接的成本取決於將外部表的每個選定行及其內部表的每個匹配行讀入記憶體的成本。最佳化器使用資料字典中的統計資訊來估計這些成本。

• 排序合併連線的成本很大程度上取決於將所有源讀入記憶體並進行排序的成本。

• 雜湊連線的成本很大程度上取決於在連線的一個輸入端構建雜湊表，並使用連線另一端的行探測它的成本。

例9-1 估計連線順序和方法的成本

從概念上講，最佳化器構造了一個連線順序和方法的矩陣，以及與每個連線順序和方法相關的成本。例如，最佳化器必須確定如何在查詢中最好地聯接date_dim 和 lineorder 表。下表顯示了方法和訂單的可能變化，以及每種方法和訂單的成本。在本例中，巢狀迴圈聯接的順序是 date_dim, lineorder 的成本最低。

表9-2 date_dim 和 lineorder 表連線的示例成本

9.2 連線 方式

連線方式是連線兩個行源的機制。

根據統計資料，最佳化器選擇估計成本最低的方法。如圖9-5 所示，每個連線方法有兩個子方法：驅動（也稱為外部）行源和被驅動（也稱為內部）行源。

9.2.1 巢狀迴圈連線 (Nested Loops Joins)

巢狀迴圈將外部資料集連線到內部資料集。

對於與單表謂詞匹配的外部資料集中的每一行，資料庫檢索內部資料集中滿足連線謂詞的所有行。如果索引可用，那麼資料庫可以使用它來訪問rowid 的內部資料集。

9.2.1.1 最佳化器 何時 考慮巢狀迴圈連線

當資料庫連線小的資料子集時，巢狀迴圈連線非常有用; 當資料庫連線大的資料集時，最佳化器模式設定為 FIRST_ROWS ，或者連線條件是訪問內部表的有效方法時，巢狀迴圈連線非常有用。

注意:

連線所期望的行數是驅動最佳化器決策的因素，而不是底層表的大小。例如，一個查詢可能連線兩個各有10 億行的表，但是由於過濾器的原因，最佳化器期望每個資料集有 5 行。

一般來說，巢狀迴圈聯接在具有聯接條件索引的小表上最有效。如果行源只有一行，如對主鍵值進行相等查詢（例如，employee_id=101 ），則聯接是一個簡單的查詢。最佳化器總是試圖將最小的行源放在第一位，使其成為驅動表。

最佳化器決定使用巢狀迴圈的因素有很多。例如，資料庫可以在一個批處理中從外部行源讀取幾行。根據檢索到的行數，最佳化器可以選擇到內部行源的巢狀迴圈或雜湊聯接。例如，如果查詢將部門聯接到驅動表employees ，並且謂詞在 employees.last_name 中指定了一個值，則資料庫可能會讀取 last_name 索引中的足夠條目，以確定是否傳遞了內部閾值。如果閾值未透過，最佳化器將選擇到部門的巢狀迴圈聯接，如果閾值透過，則資料庫將執行雜湊聯接，這意味著讀取其餘員工，將其雜湊到記憶體中，然後加入到部門。

如果內部迴圈的訪問路徑不依賴於外部迴圈，則結果可以是笛卡爾積：對於外部迴圈的每次迭代，內部迴圈生成相同的行集。若要避免此問題，請使用其他聯接方法聯接兩個獨立的行源。

9.2.1.2 巢狀迴圈聯接 工作原理

從概念上講，巢狀迴圈相當於兩個巢狀的for 迴圈。

例如，如果一個查詢連線了員工表和部門表，那麼虛擬碼中的巢狀迴圈可能是:

內部迴圈對外部迴圈的每一行執行。employees 表是 “ 外部 ” 資料集，因為它位於外部 for 迴圈中。外部表有時稱為驅動表。 departments 表是 “ 內部 ” 資料集，因為它位於內部的 for 迴圈中。

巢狀迴圈連線涉及以下基本步驟:

1. 最佳化器確定驅動行源並將其指定為外部迴圈。

外層迴圈產生一組用於驅動連線條件的行。行源可以是使用索引掃描、全表掃描或任何其他生成行的操作訪問的表。

內部迴圈的迭代次數取決於外部迴圈中檢索到的行數。例如，如果從外部表檢索10 行，那麼資料庫必須在內部表中執行 10 次查詢。如果從外部表檢索到 10,000,000 行，那麼資料庫必須在內部表中執行 10,000,000 個查詢。

2. 最佳化器將另一個行源指定為內部迴圈。

外部迴圈出現在執行計劃的內部迴圈之前，具體如下:

NESTED LOOPS

outer_loop

inner_loop

3. 對於客戶端的每次取數請求，基本過程如下：

a 、從外部行源獲取行

b 、探測內部行源以查詢與謂詞條件匹配的行

c 、重複前面的步驟，直到獲取請求獲得所有行

有時資料庫會對rowid 進行排序，以獲得更有效的緩衝區訪問模式。

9.2.1.3 巢狀 巢狀迴圈

巢狀迴圈的外部迴圈本身可以是由不同的巢狀迴圈生成的行源。

資料庫可以巢狀兩個或更多的外部迴圈，以便根據需要聯接儘可能多的表。每個迴圈都是一個資料訪問方法。下面的模板展示了資料庫是如何遍歷三個巢狀迴圈的:

資料庫對迴圈的排序如下:

1. 資料庫遍歷巢狀迴圈 1:

巢狀迴圈1 的輸出是一個行源。

2. 資料庫遍歷巢狀迴圈 2 ，使用巢狀迴圈 1 生成的行源作為它的外迴圈 :

巢狀迴圈2 的輸出是另一個行源。

3. 資料庫遍歷巢狀迴圈 3 ，使用巢狀迴圈 2 生成的行源作為它的外迴圈 :

例9-2 巢狀巢狀迴圈聯接

假設您按如下方式連線employees 和 departments 表：

SELECT /*+ ORDERED USE_NL(d) */

e.last_name , e.first_name , d.department_name

FROM employees e , departments d

WHERE e.department_id = d.department_id

AND e.last_name like 'A%' ;

該計劃顯示，最佳化器選擇了兩個巢狀迴圈( 步驟 1 和步驟 2) 訪問資料 :

在本例中，基本過程如下：

1. 資料庫開始遍歷內部巢狀迴圈（步驟 2 ），如下所示：

a 、資料庫在 emp_name_ix 中搜尋 rowids ，查詢以 a 開頭的所有姓氏（步驟 4 ）。

例如：

Abel,employees_rowid

Ande,employees_rowid

Atkinson,employees_rowid

Austin,employees_rowid

b. 使用前一步中的 rowid ，資料庫從 employees 表中檢索一批行 ( 步驟 3) ，例如 :

Abel,Ellen,80

Abel,John,50

這些行成為最內層巢狀迴圈的外部行源。

批處理步驟通常是自適應執行計劃的一部分。要確定巢狀迴圈是否優於雜湊連線，最佳化器需要確定從行源返回的許多行。如果返回了太多行，那麼最佳化器將切換到不同的連線方法。

c. 對於外部行源中的每一行，資料庫掃描dept_id_pk 索引以獲得匹配部門ID 的部門中的 rowid( 步驟 5) ，並將其連線到employees 行。例如：

Abel,Ellen,80,departments_rowid

Ande,Sundar,80,departments_rowid

Atkinson,Mozhe,50,departments_rowid

Austin,David,60,departments_rowid

這些行成為外部巢狀迴圈的外部行源( 步驟 1) 。

2. 資料庫遍歷外部巢狀迴圈，如下所示：

a 、資料庫讀取外部行源中的第一行。例如：

Abel,Ellen,80,departments_rowid

b 、資料庫使用 departments rowid 從 departments 檢索相應的行（步驟 6 ），然後連線結果以獲取請求的值（步驟 1 ）。

例如：

Abel,Ellen,80,Sales

c 、資料庫讀取外部行源中的下一行，使用 departments rowid 從 departments 檢索相應的行（步驟 6 ），並遍歷迴圈，直到檢索到所有行。

結果集的格式如下：

Abel,Ellen,80,Sales

Ande,Sundar,80,Sales

Atkinson,Mozhe,50,Shipping

Austin,David,60,IT

9.2.1.4 巢狀迴圈聯接的當前實現

Oracle Database 11g 引入了一種新的巢狀迴圈實現，它減少了物理 I/O 的總體延遲。

當索引或表塊不在緩衝區快取中並且需要處理連線時，需要物理I/O 。資料庫可以批處理多個物理 I/O 請求，並使用向量 I/O （陣列）而不是一次處理一個。資料庫向執行讀取的作業系統傳送一個 rowid 陣列。

作為新實現的一部分，兩個巢狀迴圈連線行源可能出現在執行計劃中，而在以前的版本中只有一個會出現在執行計劃中。在這種情況下，Oracle 資料庫會分配一個巢狀迴圈連線行源，將連線外部表中的值與內部的索引連線起來。第二行源被分配來聯接第一個聯接的結果，其中包括儲存在索引中的 rowid ，表位於聯接的內側。

考慮“ 巢狀迴圈聯接的原始實現 ” 中的查詢。在當前實現中，此查詢的執行計劃可能如下：

在本例中，hr.departments 表中的行構成內部巢狀迴圈（步驟 2 ）的外部行源（步驟 3 ）。索引 emp_department_ix 是內部巢狀迴圈的內部行源（步驟 4 ）。內部巢狀迴圈的結果構成外部巢狀迴圈（第 1 行）的外部行源（第 2 行）。 employees 表是外部巢狀迴圈的外部行源（第 5 行）。

對於每個fetch 請求，基本過程如下：

1. 資料庫遍歷內部巢狀迴圈（步驟 2 ）以獲得獲取中請求的行：

a 、資料庫讀取第一行部門以獲取名為 Marketing 或 Sales 的部門的部門 ID （步驟 3 ）。例如：

Marketing,20

此行集合是外部迴圈。資料庫將資料快取在PGA 中。

b 、資料庫掃描 emp_department_ix （ employees 表上的一個索引），以查詢與此 department ID 相對應的 employees 行 ID （步驟 4 ），然後連線結果集（步驟2 ）。

結果集的格式如下：

Marketing,20,employees_rowid

c 、資料庫讀取下一行部門，掃描 emp_department_ix 以找到與此部門 ID 相對應的員工行 ID ，然後遍歷迴圈，直到滿足客戶端請求。

在本例中，資料庫只在外部迴圈中迭代兩次，因為只有來自部門的兩行滿足謂詞過濾器。

從概念上講，結果集具有以下形式：

Marketing,20,employees_rowid

...

Sales,80,employees_rowid

...

這些行成為外部巢狀迴圈的外部行源（步驟1 ）。

此行集快取在PGA 中。

2. 資料庫將上一步獲得的 rowid 組織起來，以便在快取中更有效地訪問它們。

3. 資料庫開始遍歷外部巢狀迴圈，如下所示：

a 、資料庫從上一步獲得的行集合中檢索第一行，如下例所示：

Marketing,20,employees_rowid

b 、使用 rowid ，資料庫從 employees 檢索一行以獲取請求的值（步驟 1 ），如下例所示：

Michael,Hartstein,13000,Marketing

c 、資料庫從行集中檢索下一行，使用 rowid 探測匹配行的僱員，並在迴圈中迭代，直到檢索到所有行。

結果集的格式如下：

Michael,Hartstein,13000,Marketing

Pat,Fay,6000,Marketing

John,Russell,14000,Sales

Karen,Partners,13500,Sales

Alberto,Errazuriz,12000,Sales

...

在某些情況下，未分配第二個聯接行源，執行計劃與Oracle Database 11g 之前的執行計劃相同。以下列表描述了這些情況：

• 索引中存在連線內側所需的所有列，不需要表訪問。在這種情況下， Oracle 資料庫只分配一個連線行源。

• 返回的行的順序可能與 Oracle Database 12c 之前版本中返回的順序不同。因此，當 Oracle Database 嘗試保留行的特定順序（例如，為了消除按排序的順序的需要）時， Oracle Database 可能會使用巢狀迴圈聯接的原始實現。

•OPTIMIZER_FEATURES_ENABLE 初始化引數設定為Oracle Database 11g 之前的版本。在這種情況下， Oracle Database 使用原始實現進行巢狀迴圈聯接。

9.2.1.5 巢狀迴圈聯接的原始實現

在當前版本中，巢狀迴圈的新實現和原始實現都是可能的。

對於原始實現的示例，請考慮

hr.employees 和 hr.departments 表：

SELECT e.first_name , e.last_name , e.salary , d.department_name

FROM hr.employees e , hr.departments d

WHERE d.department_name IN ( 'Marketing' , 'Sales' )

AND e.department_id = d.department_id ;

在Oracle Database 11g 之前的版本中，此查詢的執行計劃可能如下所示：

對於每個fetch 請求，基本過程如下：

1. 資料庫遍歷迴圈以獲取請求的行：

a 、資料庫讀取第一行部門以獲取名為 Marketing 或 Sales 的部門的部門 ID （步驟 3 ）。例如：

Marketing,20

此行集合是外部迴圈。資料庫快取PGA 中的行。

b 、資料庫掃描 emp_department_ix （ employees.department_id 列上的索引），以查詢與此部門 id 相對應的 employees 行 id （步驟 4 ），然後連線結果集（步驟2 ）。

從概念上講，結果集具有以下形式：

Marketing,20,employees_rowid

c 、資料庫讀取下一行部門，掃描 emp_department_ix 以找到與此部門 ID 對應的員工行 ID ，並在迴圈中迭代，直到滿足客戶端請求。

在本例中，資料庫只在外部迴圈中迭代兩次，因為只有來自部門的兩行滿足謂詞過濾器。

從概念上講，結果集具有以下形式：

Marketing,20,employees_rowid

...

Sales,80,employees_rowid

...

2. 根據具體情況，資料庫可以組織上一步中獲得的快取的 row id ，以便更有效地訪問它們。

3. 對於巢狀迴圈生成的結果集中的每個 employees rowid ，資料庫從 employees 檢索一行以獲取請求的值（步驟 1 ）。

因此，基本過程是讀取一個rowid 並檢索匹配的 employees 行，讀取下一個 rowid 並檢索匹配的 employees 行，等等。從概念上講，結果集具有以下形式：

Michael,Hartstein,13000,Marketing

Pat,Fay,6000,Marketing

John,Russell,14000,Sales

Karen,Partners,13500,Sales

Alberto,Errazuriz,12000,Sales

...

9.2.1.6 巢狀迴圈 控制

可以新增USE _ NL 提示，指示最佳化器使用指定表作為內部表，使用巢狀迴圈聯接將每個指定表聯接到另一行源。

相關提示USE_NL_WITH_INDEX （ table INDEX ）提示指示最佳化器將指定表連線到另一個具有巢狀迴圈的行源，使用指定表作為內部表連線。索引是可選的。如果未指定索引，則巢狀迴圈聯接使用至少有一個聯接謂詞的索引作為索引鍵。

示例9-3 巢狀迴圈提示

假設最佳化器為以下查詢選擇雜湊聯接：

SELECT e.last_name , d.department_name

FROM employees e , departments d

WHERE e.department_id = d.department_id ;

執行計劃如下：

要使用部門作為內部表強制巢狀迴圈連線，請新增USE_NL 提示，如下面的查詢所示 :

SELECT /*+ ORDERED USE_NL(d) */

e.last_name , d.department_name

FROM employees e , departments d

WHERE e.department_id = d.department_id ;

執行計劃如下：

資料庫得到的結果集如下:

1. 在巢狀迴圈中，資料庫讀取員工 表 以獲取員工的姓和部門ID( 步驟 2) ，例如 :

De Haan,90

2. 對於上一步獲得的行，資料庫掃描 department ，查詢與 employees department ID 匹配的部門名稱 ( 步驟 3) ，並連線結果 ( 步驟 1) ，例如 :

De Haan,Executive

3. 資料庫檢索 employee 中的下一行，從 department 中檢索匹配的行，然後重複這個過程，直到檢索到所有行。結果集的形式如下 :

De Haan,Executive

Kochnar,Executive

Baer,Public Relations

King,Executive

...

9.2.2 Hash Joins

資料庫使用雜湊聯接來聯接較大的資料集。

最佳化器使用兩個資料集中較小的一個在記憶體中的聯接鍵上構建雜湊表，使用確定性雜湊函式指定雜湊表中儲存每一行的位置。然後，資料庫掃描較大的資料集，探測雜湊表以查詢滿足聯接條件的行。

9.2.2.1 最佳化器 何時會 考慮 雜湊連線

通常，當必須連線相對較大的資料量( 或必須連線小表的很大一部分 ) 時，並且連線是等值連線，最佳化器會考慮雜湊連線，。

當較小的資料集適合於記憶體時，雜湊連線的成本效率最高。在這種情況下，成本限制在兩個資料集上的一次讀取傳遞。

因為雜湊表在PGA 中，所以 Oracle 資料庫可以訪問行而不需要鎖定它們。這種技術透過避免重複鎖存和讀取資料庫緩衝區快取中的塊來減少邏輯 I/O 。

如果資料集在記憶體中不合適，那麼資料庫將對行源進行分割槽，而連線將按分割槽進行分割槽。這可能會使用大量的排序區域記憶體和I/O 到臨時表空間。這種方法仍然是最經濟有效的，特別是當資料庫使用並行查詢伺服器時。

9.2.2.2 雜湊聯接的工作原理

雜湊演算法接受一組輸入並應用確定性雜湊函式來生成隨機雜湊值。

在雜湊聯接中，輸入值是聯接鍵。輸出值是陣列（雜湊表）中的索引（slots ）。

9.2.2.2.1 雜湊表

為了演示雜湊表，假設資料庫對部門表和員工表透過部門id 關聯進行雜湊運算。

前5 行部門表資料如下：

SQL > select * from departments where rownum < 6 ;

資料庫對錶中的每個department_id 應用雜湊函式，為每個department 生成一個雜湊值。在這個例子中，雜湊表有5 個槽 ( 可以多一點，也可以少一點 ) 。因為 n 是 5 ，所以可能的雜湊值的範圍是從 1 到 5 。雜湊函式可能會為部門 id 生成以下值 :

f(10) = 4

f(20) = 1

f(30) = 4

f(40) = 2

f(50) = 5

注意，雜湊函式恰好為部門10 和 30 生成相同的雜湊值 4 。這就是所謂的雜湊衝突。在本例中，資料庫使用一個連結串列將部門10 和部門 30 的記錄放在同一個槽中。

從概念上看，雜湊表如下:

1 20,Marketing,201,1800

2 40,Human Resources,203,2400

3

4 10,Administration,200,1700 -> 30,Purchasing,114,1700

5 50,Shipping,121,1500

9.2.2.2.2 雜湊 連線: 基本步驟

最佳化器使用較小的資料來源在記憶體中的連線鍵上構建雜湊表，然後掃描較大的表來查詢連線的行。

基本步驟如下：

1. 資料庫對較小的資料集 ( 稱為構建表 ) 執行完整掃描，然後對每一行中的連線鍵應用 雜湊 函式，以構建PGA 中的 雜湊表。

在虛擬碼中，演算法可能如下所示:

2. 資料庫使用成本最低的訪問機制探測第二個資料集 ( 稱為探測表 ) 。

通常，資料庫會對更小和更大的資料集進行全面掃描。虛擬碼中的演算法可能如下所示:

對於從較大的資料集中檢索到的每一行，資料庫執行以下操作:

a. 將相同的雜湊函式應用於連線列或多個列，以計算雜湊表中相關槽的數目。

例如，為了探測部門ID 30 的雜湊表，資料庫將雜湊函式應用到 30 ，它將生成雜湊值 4 。

b. 探測雜湊表以確定槽中是否存在行。

如果不存在行，則資料庫處理較大資料集中的下一行。如果存在行，則資料庫繼續進行下一步。

c. 檢查聯接列或多個列是否匹配。如果發生匹配，那麼資料庫要麼報告這些行，要麼將它們傳遞到計劃的下一步，然後處理更大資料集中的下一行。

如果雜湊表槽中存在多行，則資料庫遍歷行連結串列，檢查每一行。例如，如果department 30 雜湊到slot 4 ，那麼資料庫將檢查每一行，直到找到 30 為止。

示例9-4 Hash Joins

應用程式查詢oe.orders 和 oe.order_items 表，關聯列是 order_i d 。

SELECT o.customer_id , l.unit_price * l.quantity

FROM orders o , order_items l

WHERE l.order_id = o.order_id ;

執行計劃如下：

因為orders 表相對於 order_items 表比較小，而 order_items 表要大 6 倍，所以資料庫會雜湊 orders 。在雜湊連線中，構建表的資料集總是首先出現在操作列表中( 步驟 2) 。

9.2.2.3 當雜湊表不適合 PGA 時，雜湊連線如何工作

當雜湊表不能完全適合PGA 時，資料庫必須使用另一種技術。在這種情況下，資料庫使用一個臨時空間來儲存雜湊表的部分 ( 稱為分割槽 ) ，有時還儲存探測雜湊表的較大表的部分。

基本流程如下:

1. 資料庫對較小的資料集執行完整的掃描，然後在 PGA 和磁碟上構建一個雜湊桶陣列。

當PGA 雜湊區填滿時，資料庫會找到雜湊表中最大的分割槽，並將其寫入磁碟上的臨時空間。資料庫將屬於這個磁碟上分割槽的任何新行儲存在磁碟上，以及 PGA 中的所有其他行。因此，雜湊表的一部分在記憶體中，另一部分在磁碟上。

2. 資料庫第一次讀取其他資料集。對於每一行，資料庫執行以下操作 :

a. 將相同的雜湊函式應用於連線列，以計算相關雜湊桶的數目。

b. 探測雜湊表，以確定記憶體中的桶中是否存在行。

如果雜湊值指向記憶體中的一行，那麼資料庫將完成連線並返回該行。但是，如果該值指向磁碟上的雜湊分割槽，那麼資料庫將這一行儲存在臨時表空間中，使用與原始資料集相同的分割槽方案。

3. 資料庫逐個讀取每個磁碟上的臨時分割槽

4. 資料庫將每個分割槽行連線到相應的磁碟上臨時分割槽中的行。

9 .2.2.4 Hash Joins 控制

USE_HASH 提示最佳化器在將兩個表連線在一起時使用雜湊聯接。

9.2.3 排序 合併連線

排序合併聯接是巢狀迴圈聯接的變體。

如果聯接中的兩個資料集尚未排序，則資料庫將對它們進行排序。這些是排序連線操作。對於第一個資料集中的每一行，資料庫將探測第二個資料集以查詢匹配的行並將它們連線起來，它的起始位置基於前一個迭代中進行的匹配。這是合併連線操作。

9.2.3.1 最佳化器 何時考慮使用 排序合併連線

雜湊連線需要一個雜湊表和這個表的一個探測，而排序合併連線需要兩個排序。

當下列條件為真時，最佳化器可以選擇排序合併連線而不是雜湊連線來連線大量資料:

• 兩個表之間的聯接條件不是等值聯接，即使用了諸如< 、 <= 、 > 或 >= 這樣的不等式條件。

與排序合併不同，雜湊連線需要一個相等條件。

• 由於其他操作需要排序，最佳化器發現使用排序合併成本更低。

如果存在索引，則資料庫可以避免對第一個資料集進行排序。但是，無論索引如何，資料庫總是對第二個資料集進行排序。

與巢狀迴圈連線相比，排序合併具有與雜湊連線相同的優點: 資料庫訪問 PGA 中的行，而不是 SGA 中的行，透過避免重複鎖存和讀取資料庫緩衝區快取中的塊，減少了邏輯 I/O 。通常，雜湊連線比排序合併連線執行得更好，因為排序是昂貴的。但是，與雜湊連線相比，排序合併連線具有以下優點 :

• 在初始排序之後，合併階段進行了最佳化，從而更快地生成輸出行。

• 當雜湊表不能完全裝入記憶體時，排序合併可能比雜湊連線更划算。

記憶體不足的雜湊連線需要將雜湊表和其他資料集複製到磁碟。在這種情況下，資料庫可能需要多次從磁碟讀取資料。在排序合併中，如果記憶體不能儲存這兩個資料集，那麼資料庫將它們都寫入磁碟，但是每個資料集的讀取次數不超過一次。

9.2.3.2 排序歸併聯接的工作原理

與巢狀迴圈聯接一樣，sort merge 聯接讀取兩個資料集，但在它們還沒有排序時對它們進行排序。

對於第一個資料集中的每一行，資料庫在第二個資料集中找到一個起始行，然後讀取第二個資料集，直到找到一個不匹配的行。在虛擬碼中，排序歸併的高階演算法可能如下所示:

例如，下表顯示了兩個資料集中的排序值:temp_ds1 和 temp_ds2 。

如下表所示，資料庫首先讀取temp_ds1 中的 10 ，然後讀取 temp_ds2 中的第一個值。因為 temp_ds2 中的 20 大於 temp_ds1 中的 10 ，所以資料庫停止讀取 temp_ds2 。

資料庫繼續處理temp_ds1 中的下一個值，即 20 。資料庫透過 temp_ds2 執行，如下表所示。

資料庫繼續到temp_ds1 中的下一行，即 30 。資料庫從最後一次匹配的數目 (20) 開始，然後透過 temp_ds2 查詢匹配，如下表所示。

資料庫繼續到temp_ds1 中的下一行，即 40 。如下表所示，資料庫從 temp_ds2 中最後一個匹配項的數量開始，即 20 個，然後透過 temp_ds2 查詢匹配項。

資料庫以這種方式繼續執行，直到它與temp_ds2 中的最後 70 個匹配為止。這個場景表明，當資料庫讀取 temp_ds1 時，不需要讀取 temp_ds2 中的每一行。與巢狀迴圈連線相比，這是一個優勢。

例9-5 使用索引對合並連線進行排序

下面的查詢將連線上的employees 和 departments 表

department_id 列，對 department_id 上的行按如下順序排序 :

SELECT e.employee_id ,

e.last_name ,

e.first_name ,

e.department_id ,

d.department_name

FROM employees e , departments d

WHERE e.department_id = d.department_id

ORDER BY department_id ;

查詢DBMS_XPLAN.DISPLAY_CURSOR顯示排序合併聯接執行計劃：

這兩個資料集是departments 表和 employees 表。由於索引按部門 id 對 departments 表進行排序，因此資料庫可以讀取此索引並避免排序（步驟 3 ）。資料庫只需要對 employees 表進行排序（步驟 4 ），這是 CPU 最密集的操作。

例9-6 沒有索引的排序合併連線

在department_id 列上聯接 employees 和 departments 表，按如下方式對 department_id 上的行進行排序。在本例中，您指定 NO_INDEX 和 USE_MERGE 來強制最佳化器選擇排序合併 :

SELECT /*+ USE_MERGE(d e) NO_INDEX(d) */

e.employee_id ,

e.last_name ,

e.first_name ,

e.department_id ,

d.department_name

FROM employees e , departments d

WHERE e.department_id = d.department_id

ORDER BY department_id ;

查詢DBMS_XPLAN.DISPLAY_CURSOR顯示排序合併聯接執行計劃：

由於忽略了departments.department_id 索引，最佳化器將執行排序，這將使步驟 2 和步驟 3 的總成本增加 67%( 從 3 增加到 5) 。

9.2.3.3 合併 排序連線控制

USE_MERGE 提示指示最佳化器使用排序合併連線。

在某些情況下，使用USE_MERGE 提示覆蓋最佳化器是有意義的。例如，最佳化器可以選擇對錶進行全掃描，並避免在查詢中執行排序操作。但是，這樣做會增加成本，因為透過索引和單個塊讀取來訪問大表，而不是透過全表掃描進行更快的訪問。

9.3 連線型別

連線型別由連線條件的型別決定。

9.3.1 內連線

內部連線( 有時稱為簡單連線 ) 是隻返回滿足連線條件的行的連線。內連線可以是等連線，也可以是非等連線。

9.3.1.1 等值連線

等值連線是一個內部連線，其連線條件包含一個等式運算子。

下面的例子是一個等值連線，因為連線條件只包含一個等式運算子:

SELECT e.employee_id , e.last_name , d.department_name

FROM employees e , departments d

WHERE e.department_id = d.department_id ;

在前面的查詢中，聯接條件是e.department_id=d.department_id。如果employees表中的某一行的部門ID與部門表中某一行的值匹配，則資料庫返回連線的結果;否則，資料庫不會返回結果。

9.3.1.2 非等值連線

非等值連線是一個內部連線，其連線條件包含一個非等值運算子。

下面的查詢列出了所有在員工176( 因為他在 2007 年換了工作，所以被列在 job_history 中 ) 在公司工作的員工的僱傭日期 :

SELECT e.employee_id , e.first_name , e.last_name , e.hire_date

FROM employees e , job_history h

WHERE h.employee_id = 176

AND e.hire_date BETWEEN h.start_date AND h.end_date ;

在前面的示例中，連線employees 和 job_history 的條件不包含相等運算子，因此它是一個非相等連線。非等值連線的情況比較少見。

請注意，雜湊連線至少需要部分等值連線。下面的SQL 指令碼包含一個相等連線條件 (e1.empno = e2.empno) 和一個不相等條件 :

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *

FROM scott.emp e1

JOIN scott.emp e2

ON ( e1.empno = e2.empno AND e1.hiredate BETWEEN e2.hiredate - 1 AND

e2.hiredate + 1 )

最佳化器為前面的查詢選擇一個雜湊連線，如下面的計劃所示:

9.3.1.3 Band 帶連線

帶連線是一種特殊型別的非等值連線，其中一個資料集中的鍵值必須位於第二個資料集的指定範圍(“ 帶 ”) 內。同一個表可以同時用作第一個和第二個資料集。

從Oracle 資料庫 12c 版本 2(12.2) 開始，資料庫可以更有效地評估帶連線。該最佳化避免了對超出定義頻帶的行進行不必要的掃描。

最佳化器使用成本評估來選擇連線方法( 雜湊、巢狀迴圈或排序合併 ) 和並行資料分佈方法。在大多數情況下，最佳化的效能與等效連線相當。

以下示例查詢的僱員的工資比每個僱員的工資少100 美元到多 100 美元。因此，頻寬為 200 美元。本例假設可以將每個員工的工資與自身進行比較。下面的查詢包括部分樣本輸出 :

SELECT e1.last_name || ' has salary between 100 less and 100 more than ' ||

e2.last_name AS "SALARY COMPARISON"

FROM employees e1 , employees e2

WHERE e1.salary BETWEEN e2.salary - 100 AND e2.salary + 100 ;

例9-7 查詢沒有帶連線最佳化

在不進行band join 最佳化的情況下，資料庫使用以下查詢計劃 :

在這個計劃中，步驟2 對 e1 行源進行排序，步驟 5 對 e2 行源進行排序。下表說明了排序後的行源。

連線首先遍歷排序輸入（e1 ），它是連線的左分支，對應於計劃的步驟 2 。原始查詢包含兩個謂詞：

•e1.sal>=e2.sal–100 ，這是第 5 步過濾器

•e1.sal>=e2.sal+100 ，這是第 4 步過濾器

對於已排序行源e1 的每次迭代，資料庫都會迭代行源 e2 ，根據步驟 5 filter e1.sal>=e2.sal–100 檢查每一行。如果行透過了步驟 5 篩選器，則資料庫將其傳送到步驟 4 篩選器，然後繼續針對步驟 5 篩選器測試 e2 中的下一行。但是，如果一行未能透過步驟 5 篩選，那麼 e2 的掃描將停止，資料庫將繼續執行 e1 的下一次迭代。

下表顯示了e1 的第一次迭代，從資料集 e1 中的 24000 （ King ）開始。資料庫確定 e2 中的第一行 24000 （ king ）透過步驟5 過濾器。然後，資料庫將行傳送到步驟 4 過濾器 e1.sal<=w2.sal+100 ，後者也透過。資料庫將此行傳送到合併行源。接下來，資料庫檢查 17000 （ Kochhar ）與步驟 5 過濾器，後者也透過了。但是，該行無法透過步驟 4 篩選器，因此被丟棄。資料庫繼續根據步驟 5 過濾器測試 17000 （ De Haan ）。

如上表所示，每個e2 行都必須透過步驟 5 篩選，因為 e2 工資是按降序排序的。因此，步驟 5 過濾器總是將行傳送到步驟 4 過濾器。因為 e2 工資是按降序排序的，所以步驟 4 過濾器必然會失敗，從 17000 開始的每一行（ Kochhar ）。之所以效率低下，是因為資料庫會針對步驟 5 篩選器（必須透過）測試 e2 中的每個後續行，然後針對步驟 4 篩選器（必須失敗）進行測試。

示例9-8 帶連線最佳化的查詢

從Oracle Database 12c Release 2 （ 12.2 ）開始，資料庫使用以下計劃最佳化帶區聯接，該計劃沒有單獨的篩選操作：

區別在於，步驟4 對兩個謂詞使用布林值和邏輯來建立單個篩選器。資料庫不對一個篩選器檢查一行，然後將其傳送到另一個行源以檢查第二個篩選器，而是對一個篩選器執行一次檢查。如果檢查失敗，則停止處理。

在本例中，查詢從e1 的第一次迭代開始， e1 以 24000 （ King ）開頭。下圖表示範圍。 23900 以下和 24100 以上的 e2 值超出範圍。

下表顯示，資料庫根據步驟4 過濾器測試 e2 的第一行，即 24000 （ King ）。行透過測試，因此資料庫傳送要合併的行。 e2 的下一行是 17000 （ Kochhar ）。該行超出範圍（範圍），因此不滿足篩選器謂詞，因此資料庫在此迭代中停止測試 e2 行。資料庫停止測試，因為 e2 的降序排序確保 e2 中的所有後續行都無法透過篩選測試。因此，資料庫可以進行 e1 的第二次迭代。

這樣，帶連線最佳化消除了不必要的處理。與在未最佳化的情況下掃描e2 中的每一行不同，資料庫只掃描至少兩行。

9.3.2 Outer Joins 外連線

外部聯接返回滿足聯接條件的所有行，還返回一個表中沒有來自另一個表的行滿足聯接條件的行。因此，外部聯接的結果集是內部聯接的超集。

在ANSI 語法中， OUTER JOIN 子句指定一個外部聯接。在 FROM 子句中，左表顯示在外部聯接關鍵字的左側，右表顯示在這些關鍵字的右側。左表也稱為外表，右表也稱為內表。例如，在下面的語句中 employees 表是左表或外表：

SELECT employee_id , last_name , first_name

FROM employees

LEFT OUTER JOIN departments

ON ( employees.department_id = departments.departments_id );

外連線要求外連線表作為驅動表。在前面的例子中，employees 是驅動表，departments 是被驅動表。

9.3.2.1 巢狀迴圈外連線

資料庫使用此操作來迴圈兩個表之間的外部連線。外部連線返回外部( 保留的 ) 錶行，即使內部 ( 可選 ) 表中沒有對應的行。

在一個標準的巢狀迴圈中，最佳化器根據成本選擇表的順序，即驅動表和被驅動表。但是，在巢狀迴圈外部連線中，連線條件決定表的順序。資料庫使用外部的行保留表來驅動內部表。

在以下情況下，最佳化器使用巢狀迴圈連線來處理外部連線:

• 可以從外部表驅動到內部表。

• 資料量足夠低，使巢狀迴圈方法有效。

對於巢狀迴圈外部連線的一個示例，您可以將USE_NL 提示新增到示例 9-9 中，以指示最佳化器使用巢狀迴圈。例如 :

SELECT /*+ USE_NL(c o) */

cust_last_name , SUM ( NVL2 ( o.customer_id , 0 , 1 )) "Count"

FROM customers c , orders o

WHERE c.credit_limit > 1000

AND c.customer_id = o.customer_id (+)

GROUP BY cust_last_name ;

9.3.2.2 雜湊連線外部連線

當資料量大到足以使雜湊連線有效時，或者無法從外部表驅動到內部表時，最佳化器將使用雜湊連線來處理外部連線。

成本決定了表格的順序。外部表( 包括保留的行 ) 可以用來構建雜湊表，也可以用來探測雜湊表。

例9-9 雜湊連線外部連線

這個示例展示了一個典型的雜湊連線外部連線查詢及其執行計劃。在這個例子中，所有信用限額大於1000 的客戶都被查詢。需要一個外部連線，以便查詢捕獲沒有訂單的客戶。

• 外部表是customers 。

• 內部表是 orders 。

• 聯接保留 customers 資料，包括orders 中沒有對應行的那些行。

您可以使用不存在的子查詢來返回行。但是，由於要查詢表中的所有行，所以雜湊連線的效能更好( 除非不存在的子查詢沒有巢狀 ) 。

SELECT cust_last_name , SUM ( NVL2 ( o.customer_id , 0 , 1 )) "Count"

FROM customers c , orders o

WHERE c.credit_limit > 1000

AND c.customer_id = o.customer_id (+)

GROUP BY cust_last_name ;

查詢查詢滿足各種條件的客戶。當外部聯接在內部表中沒有找到任何對應的行時，它將為內部表列和外部( 保留的 ) 錶行返回 NULL 。此操作查詢沒有任何訂單行的所有客戶行。

在這種情況下，外部連線條件如下:

customers.customer_id = orders.customer_id(+)

該條件的組成部分如下:

例9-10 外連線到多表檢視

在本例中，外部連線是一個多表檢視。最佳化器不能像普通連線或謂詞推進那樣插入檢視，因此它構建檢視的整個行集。

SELECT c.cust_last_name , sum ( revenue )

FROM customers c , v_orders o

WHERE c.credit_limit > 2000

AND o.customer_id (+) = c.customer_id

GROUP BY c.cust_last_name ;

檢視定義如下：

CREATE OR REPLACE view v_orders AS

SELECT l.product_id ,

SUM ( l.quantity * unit_price ) revenue ,

o.order_id ,

o.customer_id

FROM orders o , order_items l

WHERE o.order_id = l.order_id

GROUP BY l.product_id , o.order_id , o.customer_id ;

9.3.2.3 排序合併外部連線

當外部聯接無法從外部（保留的）表驅動到內部（可選）表時，它不能使用雜湊聯接或巢狀迴圈聯接。

在這種情況下，它使用sort-merge 外部聯接。

最佳化器在以下情況下對外部聯接使用排序合併：

• 巢狀迴圈連線效率低下。由於資料量的原因，巢狀迴圈聯接可能效率低下。

• 最佳化器發現在雜湊連線上使用排序合成本更低，因為其他操作需要排序。

9.3.2.4 全外連線

完全外部聯接是左外部聯接和右外部聯接的組合。

除了內部聯接之外，兩個表中未在內部聯接結果中返回的行也將保留並擴充套件為空。換句話說，完全外部聯接將表聯接在一起，但在聯接的表中顯示沒有相應行的行。

示例9-11 完全外部連線

以下查詢檢索每個部門中的所有部門和所有員工，但也包括：

• 任何沒有部門的員工

• 任何沒有員工的部門

SELECT d.department_id , e.employee_id

FROM employees e

FULL OUTER JOIN departments d

ON e.department_id = d.department_id

ORDER BY d.department_id ;

結果如下：

例9-12 完整外部連線的執行計劃

從Oracle Database 11g 開始， Oracle Database 將自動使用基於雜湊連線的本機執行方法來執行完整的外部連線。當資料庫使用新方法執行完整外部連線時，查詢的執行計劃包含雜湊連線完整外部。例 9-11 中的查詢使用以下執行計劃 :

在前面的計劃( 步驟 3) 中包含了雜湊連線完整外部，這表明查詢使用雜湊完整外部連線執行方法。通常，當兩個表之間的全外連線條件是等值連線時，可以使用雜湊全外連線執行方法，Oracle 資料庫會自動使用它。

要指示最佳化器考慮使用雜湊全外連線執行方法，請應用NATIVE_FULL_OUTER_JOIN 提示。要指示最佳化器不要考慮使用雜湊完整外連線執行方法，請應用 NO_NATIVE_FULL_OUTER_JOIN 提示。 NO_NATIVE_FULL_OUTER_JOIN 提示指示最佳化器在連線每個指定表時排除本機執行方法。相反，完整的外部連線是作為左外部連線和反連線的聯合執行的。

9.3.2.5 外部連線左側有多個表

在Oracle 資料庫 12c 中，一個外連線表的左側可能存在多個表。

此增強功能使Oracle 資料庫能夠合併包含多個表的檢視，並顯示在外部連線的左側。在 Oracle 資料庫 12c 之前的版本中，像下面這樣的查詢是無效的，並且會觸發 ORA-01417 錯誤訊息 :

SELECT t1.d , t3.c

FROM t1 , t2 , t3

WHERE t1.z = t2.z

AND t1.x = t3.x (+)

AND t2.y = t3.y (+);

從Oracle 資料庫 12c 開始，前面的查詢是有效的。

9.3.3 半連線Semijoins

半聯接是兩個資料集之間的聯接，當子查詢資料集中存在匹配的行時，該聯接返回第一個資料集中的行。

資料庫在第一次匹配時停止處理第二個資料集。因此，當第二個資料集中的多行滿足子查詢條件時，最佳化不會複製第一個資料集中的行。

注：

半聯接 Semijoins 和反聯接antijoins 被視為聯接型別，即使導致它們的 SQL 構造是子查詢。它們是內部演算法，最佳化器使用它們來展開子查詢結構，以便它們可以以類聯接的方式進行解析。

9.3.3.1 最佳化器 何時 考慮半聯接

當查詢只需要確定匹配是否存在時，半聯接避免返回大量行。

對於大型資料集，這種最佳化可以大大節省巢狀迴圈聯接的時間，巢狀迴圈聯接必須遍歷內部查詢為外部查詢中的每一行返回的每條記錄。最佳化器可以將半聯接最佳化應用於巢狀迴圈聯接、雜湊聯接和排序合併聯接。

最佳化器可以在以下情況下選擇半聯接：

• 語句使用 IN 或 EXISTS 子句。

• 語句在 in 或 EXISTS 子句中包含子查詢。

•IN 或 EXISTS 子句不包含在 or 分支中。

9.3.3.2 半連線的工作原理

根據使用的連線型別，半連線最佳化的實現方式不同。

以下虛擬碼顯示巢狀迴圈聯接的半聯接：

在前面的虛擬碼中，ds1 是第一個資料集， ds2_subquery 是子查詢資料集。程式碼從第一個資料集獲取第一行，然後迴圈子查詢資料集以查詢匹配項。程式碼一旦找到匹配項就退出內部迴圈，然後開始處理第一個資料集中的下一行。

示例9-13 使用 WHERE EXISTS 的半聯接

以下查詢使用WHERE EXISTS 子句僅列出包含員工的部門：

SELECT department_id , department_name

FROM departments

WHERE EXISTS

( SELECT 1

FROM employees

WHERE employees.department_id = departments.department_id )

執行計劃在步驟1 中顯示巢狀迴圈半連線：

對於構成外部迴圈的departments 中的每一行，資料庫獲取 department ID ，然後探測 employees.department_ID 索引以查詢匹配條目。從概念上講，索引如下：

10,rowid

30,rowid

...

如果departments 表中的第一個條目是 department 30 ，則資料庫將對索引執行範圍掃描，直到找到第一個 30 條目，然後停止讀取索引並從 departments 返回匹配的行。如果外層迴圈中的下一行是 department 20 ，那麼資料庫將掃描索引以查詢 20 條目，但找不到任何匹配項，然後執行外層迴圈的下一次迭代。資料庫以這種方式進行，直到返回所有匹配的行。

示例9-14 在中使用的半聯接

以下查詢使用IN 子句僅列出包含員工的部門：

SELECT department_id , department_name

FROM departments

WHERE department_id IN ( SELECT department_id FROM employees );

執行計劃在步驟1 中顯示巢狀迴圈半連線：

該計劃與例9-13 中的計劃相同。

9.3.4 反連線 Antijoins

反聯接是兩個資料集之間的聯接，當子查詢資料集中不存在匹配的行時，反聯接返回第一個資料集中的行。

與半聯接一樣，反聯接在找到第一個匹配項時停止處理子查詢資料集。與半聯接不同，反聯接只在找不到匹配項時返回行。

9.3.4.1 最佳化器 何時 考慮反聯接

當查詢只需要在不存在匹配時返回行時，反聯接避免了不必要的處理。

對於大型資料集，這種最佳化可以比巢狀迴圈連線節省大量時間。後者透過外部查詢中每一行的內部查詢返回的每條記錄進行迴圈。最佳化器可以將反聯接最佳化應用於巢狀迴圈聯接、雜湊聯接和排序合併聯接

最佳化器可以在以下情況下選擇反聯接：

• 語句使用 NOT IN 或 NOT EXISTS 子句。

• 語句在 NOT in 或 NOT EXISTS 子句中有一個子查詢。

•NOT IN 或 NOT EXISTS 子句未包含在或分支中。

語句執行外連線並對聯接列應用IS NULL 條件，如下例所示：

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT emp. *

FROM emp , dept

WHERE emp.deptno = dept.deptno (+)

AND dept.deptno IS NULL

9.3.4.2 反連線的工作原理

根據使用的連線型別，反連線最佳化的實現方式不同。

以下虛擬碼顯示巢狀迴圈聯接的反聯接：

在前面的偽碼中，ds1 是第一個資料集， ds2 是第二個資料集。程式碼從第一個資料集獲取第一行，然後迴圈透過第二個資料集尋找匹配項。程式碼一旦找到匹配項就退出內部迴圈，並開始處理第一個資料集中的下一行。

示例9-15 使用 WHERE EXISTS 的半聯接

以下查詢使用WHERE EXISTS 子句僅列出包含員工的部門：

SELECT department_id , department_name

FROM departments

WHERE EXISTS

( SELECT 1

FROM employees

WHERE employees.department_id = departments.department_id )

顯示了巢狀迴圈半操作在步驟1 的執行計劃:

對於形成外部迴圈的部門中的每一行，資料庫獲取部門ID ，然後探測 employee .department_id 索引，以查詢匹配的條目。從概念上看，索引資料如下:

10,rowid

30,rowid

...

如果departments 表中的第一個記錄是 department 30 ，那麼資料庫將對索引執行一次範圍掃描，直到找到第一個 30 項為止，這時它將停止讀取索引並返回來自部門的匹配行。如果外層迴圈中的下一行是 department 20 ，那麼資料庫將掃描索引中的 20 項，並沒有找到任何匹配項，從而執行外層迴圈的下一次迭代。資料庫以這種方式處理，直到返回所有匹配的行。

9.3.4.3 反連線如何處理空值

對於半連線，IN 和 EXISTS 在功能上是等價的。但是，由於為空， NOT IN 和 NOT EXISTS 在功能上是不等價的。

如果將空值返回給NOT IN 運算子，則該語句將不返回任何記錄。要檢視原因，請考慮以下 WHERE 子句 :

WHERE department_id NOT IN ( null , 10 , 20 )

資料庫對前面的表示式進行如下測試:

WHERE ( department_id != null )

AND ( department_id != 10 )

AND ( department_id != 20 )

為了使整個表示式為真，每個條件都必須為真。但是，一個空值不能與另一個值進行比較，所以department_id !=null 條件不能為真，因此整個表示式總是假的。即使在 NOT IN 運算子返回空值時，下列技術也可以使語句返回記錄 :

• 對子查詢返回的列應用 NVL 函式。

• 在子查詢中新增一個非空謂詞。

實現NOT NULL 約束。

與NOT In 不同， NOT EXISTS 子句只考慮返回匹配項存在的謂詞，而忽略任何不匹配或由於 null 而無法確定的行。如果子查詢中至少有一行與來自外部查詢的行匹配，則 NOT EXISTS 返回 false 。如果沒有元組匹配，則 NOT EXISTS 返回 true 。子查詢中是否存在空值並不影響對匹配記錄的搜尋。

在Oracle Database 11g 之前的版本中，當可以透過子查詢返回空值時，最佳化器不能使用反連線最佳化。然而，從 Oracle 資料庫 11g 開始，下面幾節中描述的 ANTI NA ( 和 ANTI SNA) 最佳化使最佳化器能夠在可能為空的情況下使用反連線。

例9-16 反連線使用 NOT IN

假設使用者使用NOT IN 子句發出以下查詢，以列出不包含僱員的部門 :

SELECT department_id , department_name

FROM departments

WHERE department_id NOT IN ( SELECT department_id FROM employees );

前面的查詢不返回行，即使幾個部門不包含僱員。由於employees.department_id 列可為空，因此發生了使用者不希望出現的此結果。

執行計劃在步驟2 中顯示巢狀迴圈反 SNA 操作：

ANTI-SNA 代表 “ 單個支援空值的 antijoin” ， ANTI-NA 代表 “ 支援空值的 antijoin” ，支援空值的操作使最佳化器可以在可空列上使用 antijoin 最佳化。在 Oracle Database 11g 之前的版本中，當可能為空時，資料庫無法對不在查詢執行反聯接。

假設使用者透過對子查詢應用IS NOT NULL 條件重寫查詢：

SELECT department_id , department_name

FROM departments

WHERE department_id NOT IN

( SELECT department_id FROM employees WHERE department_id IS NOT NULL );

前面的查詢返回16 行，這是預期的結果。計劃中的步驟 1 顯示標準巢狀迴圈反聯接，而不是反 NA 或反 SNA 聯接，因為子查詢不能返回空：

例9-17 反連線使用 NOT EXISTS

假設使用者使用一個NOT EXISTS 子句發出以下查詢，以列出不包含僱員的部門 :

SELECT department_id , department_name

FROM departments d

WHERE NOT EXISTS

( SELECT null FROM employees e WHERE e.department_id = d.department_id )

前面的查詢避免了NOT IN 子句的 null 問題。因此 , 儘管 department_id 列可為空，該語句返回所需的結果。

執行計劃的第1 步揭示了一個巢狀的迴圈反操作，而不是反 NA ，這是在可能為空時不進入所必需的：

9.3.5 笛卡爾連線 Cartesian Joins

當一個或多個表對語句中的任何其他表沒有任何聯接條件時，資料庫使用笛卡爾聯接。

最佳化器將來自一個資料來源的每一行與來自另一個資料來源的每一行連線起來，建立這兩個集合的笛卡爾積。因此，使用以下公式計算聯接產生的行總數，其中rs1 是第一行集中的行數， rs2 是第二行集中的行數：

rs1 X rs2 = total rows in result set

9.3.5.1 最佳化器 何時 考慮笛卡爾連線時

最佳化器僅在特定情況下對兩行源使用笛卡爾連線。通常，情況如下：

• 不存在連線條件。

在某些情況下，最佳化器可以在兩個表之間選取一個公共篩選條件作為可能的聯接條件。

注：

如果笛卡爾連線出現在查詢計劃中，則可能是由於不小心忽略了連線條件。一般來說，如果查詢連線n 個表，則需要 n-1 連線條件來避免笛卡爾連線。

• 笛卡爾連線是一種有效的方法。

例如，最佳化器可能決定生成兩個非常小的表的笛卡爾積，這兩個表都連線到同一個大表。

•ORDERED 提示在指定聯接表之前指定表。

9.3.5.2 笛卡爾連線的工作原理

笛卡爾連線使用巢狀迴圈。

在較高階別上，笛卡爾連線的演算法如下所示，其中ds1 通常是較小的資料集， ds2 是較大的資料集：

示例9-18 笛卡爾連線

在本例中，使用者打算執行employees 和 departments 表的內部聯接，但意外地忽略了聯接條件：

SELECT e.last_name , d.department_name FROM employees e , departments d

執行計劃如下：

在前面計劃的步驟1 中，笛卡爾關鍵字指示笛卡爾連線的存在。行數（ 2889 ）是 27 和 107 的乘積。

在步驟3 中，緩衝區排序操作指示資料庫正在將透過掃描 emp_name_ix 獲得的資料塊從 SGA 複製到 PGA 。此策略避免了對資料庫緩衝區快取中相同塊的多次掃描，這將生成許多邏輯讀取並允許資源爭用。

9.3.5.3 笛卡爾 連線的控制

ORDERED 提示指示最佳化器按照表在 FROM 子句中出現的順序連線表。透過在兩個沒有直接連線的行源之間強制連線，最佳化器必須執行笛卡爾連線。

示例 9-19 ORDERED Hint

在以下示例中，ORDERED 提示指示最佳化器聯接 employees 和 locations ，但沒有聯接條件連線這兩個行源：

SELECT /*+ORDERED*/

e.last_name , d.department_name , l.country_id , l.state_province

FROM employees e , locations l , departments d

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id

下面的執行計劃顯示了位置（步驟6 ）和員工（步驟 4 ）之間的笛卡爾產品（步驟 3 ），然後將其連線到部門表（步驟 2 ）：

9.4 連線 最佳化

連線最佳化使連線更加有效。

9.4.1 Bloom 過濾器

Bloom filter 以其建立者 Burton Bloom 的名字命名，是一種用於測試集合成員資格的低記憶體資料結構。

一個Bloom filter 可以正確地指示一個元素何時不在集合中，但也可以不正確地指示一個元素何時在集合中。因此，不可能出現漏報，但可能出現誤報。

9.4.1.1 Bloom 過濾器的用途

Bloom 過濾器測試一組值，以確定它們是否是另一組值的成員。

例如，一個集合是(10,20,30,40) ，另一個集合是 (10,30,60,70) 。一個 Bloom filter 可以確定 60 和 70 被排除在第一個集合之外， 10 和 30 可能是成員。當儲存篩選器所需的記憶體相對於資料集中的資料量比較小，並且大多數資料預期無法透過成員資格測試時， Bloom 篩選器特別有用。

Oracle 資料庫使用 Bloom filter 來實現各種特定的目標，包括 :

• 減少在並行查詢中傳輸到從屬程式的資料量，特別是當資料庫因為沒有滿足聯接條件而丟棄大多數行時

• 在連線中構建分割槽訪問列表時，刪除不需要的分割槽，這稱為分割槽修剪

• 測試資料是否存在於伺服器結果快取中，從而避免磁碟讀取

•Exadata 單元中的篩選器成員，特別是在將一個大的事實表和一個星型模式中的小維度表連線起來時，可以同時進行並行處理和序列處理。

9.4.1.2 Bloom 過濾器的工作原理

Bloom 過濾器使用一個位元陣列來表示集合中的包含。

例如，陣列中的8 個元素 ( 本例中使用的任意數字 ) 最初設定為 0:

e1 e2 e3 e4 e5 e6 e7 e8

0 0 0 0 0 0 0 0

這個陣列表示一個集合。為了表示這個陣列中的輸入值i ，對 i 應用了三個獨立的雜湊函式 ( 三個是任意的 ) ，每個函式生成 1 到 8 之間的雜湊值 :

f1(i) = h1

f2(i) = h2

f3(i) = h3

例如，為了在這個陣列中儲存值17 ，雜湊函式將 i 設定為 17 ，然後返回以下雜湊值 :

f1(17) = 5

f2(17) = 3

f3(17) = 5

在前面的示例中，兩個雜湊函式碰巧返回了相同的值5 ，這稱為雜湊衝突。因為不同的雜湊值是 5 和 3 ，所以陣列中的第 5 和第 3 個元素被設定為 1:

e1 e2 e3 e4 e5 e6 e7 e8

0 0 1 0 1 0 0 0

測試集合中17 個成員的成員資格將逆轉該過程。要測試集合是否排除值 17 ，元素 3 或元素 5 必須包含 0 。如果任何一個元素中都有 0 ，那麼集合就不能包含 17 。不可能有假陰性。

為了測試集合是否包含17 ，元素 3 和元素 5 都必須包含 1 個值。但是，如果測試表明兩個元素都是 1 ，那麼集合仍然可能不包含 17 。誤報是可能的。例如，下面的陣列可能表示值 22 ，其中元素 3 和元素 5 都有一個 1:

e1 e2 e3 e4 e5 e6 e7 e8

1 0 1 0 1 0 0 0

9.4.1.3 Bloom 過濾器控制

最佳化器會自動決定是否使用Bloom 過濾器。要覆蓋最佳化器決策，請使用提示 PX_JOIN_FILTER 和 NO_PX_JOIN_FILTER 。

9.4.1.4Bloom 濾後設資料

V$ views 包含關於 Bloom 過濾器的後設資料。您可以查詢以下檢視 :

•V$SQL_JOIN_FILTER

這個檢視顯示了由活動的Bloom 過濾器過濾出 ( 過濾列 ) 和測試 ( 探測列 ) 的行數。

•V$PQ_TQSTAT

此檢視顯示在執行樹的每個階段，透過每個並行執行伺服器處理的行數。您可以使用它來監視Bloom 過濾器減少了多少並行程式之間的資料傳輸。

在執行計劃中，Bloom 過濾器由 Operation 列中的關鍵字 JOIN filter 和 Name 列中的字首 :BF 表示，如下面的計劃片段的第 9 步所示 :

單個伺服器程式掃描時間表（步驟13 ），然後使用混合雜湊分佈方法將行傳送到並行執行伺服器（步驟 12 ）。集合 Q1,03 中的過程建立一個 bloom 過濾器（步驟 9 ）。集合 Q1,02 中的程式並行掃描 sales （步驟 18 ），然後使用 Bloom 過濾器丟棄 sales 中的行（步驟 16 ），然後使用混合雜湊分佈將它們傳送到集合 Q1,03 （步驟 15 ）。集合 Q1,03 雜湊中的程式將時間行連線到篩選的銷售行（步驟 8 ）。集合 Q1,01 中的程式掃描產品（步驟 7 ），然後將行傳送到 Q1,03 （步驟 5 ）。最後， Q1,03 中的程式將產品行與前一個雜湊聯接生成的行聯接（步驟 3 ）。