雜湊 join（hash join）演算法瞄準的正是巢狀迴圈 join 的弱點：執行內層查詢時大量的 B-tree 走訪。

它的做法是把 join 一側的候選記錄載入一張雜湊表（hash table），另一側的每一列都能對它極快地探測（probe）。

調校雜湊 join 需要與巢狀迴圈 join 完全不同的索引思路：不需要為 join 欄位建索引，只有針對獨立 where 述詞的索引才能改善雜湊 join 效能。

只有獨立述詞值得索引#

看以下例子——選出過去六個月的所有銷售與對應的員工細節：

SELECT *
  FROM sales s
  JOIN employees e ON (s.subsidiary_id = e.subsidiary_id
                  AND s.employee_id = e.employee_id )
 WHERE s.sale_date > trunc(sysdate) - INTERVAL '6' MONTH

SALE_DATE 篩選是唯一的獨立 where 子句——它只涉及一張表，且不屬於 join 述詞。

--------------------------------------------------------------
| Id | Operation          | Name      | Rows  | Bytes | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT    |           | 49244 |   59M | 12049|
|* 1 | HASH JOIN          |           | 49244 |   59M | 12049|
| 2 |    TABLE ACCESS FULL| EMPLOYEES | 10000 |    9M |   478|
|* 3 |   TABLE ACCESS FULL| SALES     | 49244 |   10M | 10521|
--------------------------------------------------------------

Predicate Information:
   1 - access("S"."SUBSIDIARY_ID"="E"."SUBSIDIARY_ID"
          AND "S"."EMPLOYEE_ID" ="E"."EMPLOYEE_ID")
   3 - filter("S"."SALE_DATE">TRUNC(SYSDATE@!)
                           -INTERVAL'+00-06' YEAR(2) TO MONTH)

執行步驟是：

  1. 全表掃描 EMPLOYEES，把所有員工載入雜湊表（plan id 2），以 join 述詞為鍵。
  2. 再對 SALES 做一次全表掃描，丟棄不符合 SALE_DATE 條件的銷售（plan id 3）。
  3. 對剩下的銷售記錄，存取雜湊表載入對應的員工細節。

雜湊表唯一的用途，就是作為暫時的記憶體結構，避免反覆存取 EMPLOYEE 表。它一次就整批載入，因此不需要索引來高效抓取單筆記錄。述詞資訊也證實：EMPLOYEES 表上沒有套用任何篩選。

這不代表雜湊 join 無法索引——可以索引的是獨立述詞，也就是在兩次資料表存取其中之一時套用的條件。以上例而言就是 SALE_DATE：

CREATE INDEX sales_date ON sales (sale_date);
--------------------------------------------------------------
| Id | Operation                    | Name       | Bytes| Cost|
--------------------------------------------------------------
| 0 | SELECT STATEMENT              |            |  59M | 3252|
|* 1 | HASH JOIN                    |            |  59M | 3252|
| 2 |    TABLE ACCESS FULL          | EMPLOYEES  |   9M |  478|
| 3 |    TABLE ACCESS BY INDEX ROWID| SALES      |  10M | 1724|
|* 4 |    INDEX RANGE SCAN          | SALES_DATE |      |     |
--------------------------------------------------------------

EMPLOYEES 仍然是全表掃描，因為查詢在該表上沒有任何獨立 where 述詞。

與巢狀迴圈 join 相反，為雜湊 join 建索引是對稱的：join 順序不影響索引策略。即使把 join 順序反轉，SALES_DATE 索引一樣能用來載入雜湊表。

縮小雜湊表：另一條優化路線#

另一種截然不同的優化思路是把雜湊表縮到最小。這之所以有效，是因為只有整張雜湊表放得進記憶體時，才可能有最佳的雜湊 join。最佳化工具因此會自動選擇 join 中較小的那一側來建雜湊表。

Oracle 執行計畫的 Bytes 欄位顯示估計的記憶體需求。上面的計畫中 EMPLOYEES 需要 9 MB，是較小的一側。

方法一：加條件。 改寫 SQL、加上額外條件，讓資料庫載入更少的候選記錄。延續上例，可以在 DEPARTMENT 屬性上加篩選、只考慮業務人員。

即使 DEPARTMENT 上沒有索引，這仍能改善雜湊 join 效能——因為資料庫不必把「不可能有銷售的員工」存進雜湊表。

但這麼做時，你必須確定不存在「不屬於該部門的員工卻有 SALES 記錄」的情況。用約束（constraint）來守護你的假設。

方法二：選更少的欄位。 縮小雜湊表時，關鍵因素不是列數，而是記憶體佔用。因此只選你真正需要的欄位：

SELECT s.sale_date, s.eur_value
     , e.last_name, e.first_name
  FROM sales s
  JOIN employees e ON (s.subsidiary_id = e.subsidiary_id
                  AND s.employee_id = e.employee_id )
 WHERE s.sale_date > trunc(sysdate) - INTERVAL '6' MONTH

這個方法很少引入 bug（拿掉錯誤的欄位通常很快就會報錯），卻能大幅削減雜湊表——本例從 9 MB 降到 234 KB，減少了 97%：

--------------------------------------------------------------
| Id | Operation                    | Name       | Bytes| Cost|
--------------------------------------------------------------
| 0 | SELECT STATEMENT              |            |2067K | 2202|
|* 1 | HASH JOIN                    |            |2067K | 2202|
| 2 |    TABLE ACCESS FULL          | EMPLOYEES  | 234K |  478|
| 3 |    TABLE ACCESS BY INDEX ROWID| SALES      | 913K | 1724|
|* 4 |    INDEX RANGE SCAN          | SALES_DATE |      |  133|
--------------------------------------------------------------

ORM 的難題：部分物件#

從 SQL 敘述裡拿掉幾個欄位乍看簡單，但用 ORM 工具時卻是真正的挑戰——對所謂**部分物件（partial object）**的支援非常稀薄。

各 ORM 如何做部分載入

Java（JPA / Hibernate）

JPA 在 @Basic 註解中定義了 FetchType.LAZY，可套用在屬性層級：

@Column(name="junk")
@Basic(fetch=FetchType.LAZY)
private String junk;

但 JPA provider 可以自由忽略它：

LAZY 策略只是給持久化 provider 執行期的一個提示，表示資料應在首次存取時才惰性抓取。實作允許對標記了 LAZY 提示的資料進行積極抓取。

——EJB 3.0 JPA，9.1.18 節

Hibernate 3.6 以編譯期位元組碼插樁實作惰性屬性抓取：在編譯後的類別中加入額外程式碼，讓 LAZY 屬性直到被存取才抓取。

這個做法對應用程式完全透明，卻打開了新一維的 N+1 問題：每筆記錄、每個屬性各一次 select。尤其危險的是，JPA 並未提供執行期控制以在需要時改為積極抓取。

Hibernate 的原生查詢語言 HQL 用 FETCH ALL PROPERTIES 子句解決此問題：

select s from Sales s FETCH ALL PROPERTIES
 inner join fetch s.employee e FETCH ALL PROPERTIES
 where s.saleDate > :dt

另一個只載入選定欄位的選項，是改用**資料傳輸物件（DTO）**而非實體。HQL 與 JPQL 寫法相同——在查詢中初始化一個物件：

select new SalesHeadDTO(s.saleDate , s.eurValue
                       ,e.firstName, e.lastName)
  from Sales s
  join s.employee e
 where s.saleDate > :dt

查詢只選出所需資料，回傳一個 SalesHeadDTO——單純的 Java 物件（POJO），而非實體。

Perl（DBIx::Class）

DBIx::Class 不作為 entity manager，因此繼承不會造成別名（aliasing）問題。可以把 Sales 類別定義成兩層：

package UseTheIndexLuke::Schema::Result::SalesHead;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('sales');
__PACKAGE__->add_columns(qw/sale_id employee_id subsidiary_id
                             sale_date eur_value/);
__PACKAGE__->set_primary_key(qw/sale_id/);
__PACKAGE__->belongs_to('employee', 'Employees',
           {'foreign.employee_id'   => 'self.employee_id'
           ,'foreign.subsidiary_id' => 'self.subsidiary_id'});

package UseTheIndexLuke::Schema::Result::Sales;
use base qw/UseTheIndexLuke::Schema::Result::SalesHead/;
__PACKAGE__->table('sales');
__PACKAGE__->add_columns(qw/junk/);

Sales 衍生自 SalesHead 並補上缺少的屬性，兩個類別可依需要使用（注意衍生類別中也必須設定 table）。接著可只選取需要的欄位：

my @sales =
   $schema->resultset('SalesHead')
          ->search($cond
                  ,{      join => 'employee'
                   ,'+columns' => ['employee.first_name'
                                   ,'employee.last_name']
                   }
                  );

PHP（Doctrine 2）

Doctrine 2 支援執行期選擇屬性。文件指出部分載入的物件可能行為古怪，因此要求用 partial 關鍵字來確認風險；此外必須明確選出主鍵欄位：

$qb = $em->createQueryBuilder();
$qb->select('partial s.{sale_id, sale_date, eur_value},'
          . 'partial e.{employee_id, subsidiary_id, '
                     . 'first_name , last_name}')
   ->from('Sales', 's')
   ->join('s.employee', 'e')
   ->where("s.sale_date > :dt")
   ->setParameter('dt', $dt, Type::DATETIME);

產生的 SQL 除了所要的欄位外，還會再帶上 SALES 表的 SUBSIDIARY_ID 與 EMPLOYEE_ID。回傳的物件與完整載入的物件相容，但缺少的欄位維持未初始化——存取它們不會拋出例外。

Backward 巢狀迴圈 排序合併 Forward