DB2 CREATE INDEX 語法詳解：選項、規則、雙向掃描與前綴掃描特性

標準語法圖

CREATE INDEX index_name ON table_name
( column_name [ASC | DESC] [, ...] )
[ ALLOW REVERSE SCANS ]  -- 隱含支援，無需指定
[ PCTFREE integer ]      -- 頁面預留空間百分比 (0-99)
[ MINPCTUSED integer ]   -- 頁面合併閾值 (線上重組相關)
[ COMPRESS YES | NO ]    -- 索引壓縮 (需 License)
[ COLLECT STATISTICS ]   -- 建立時自動收集統計 (DETAILED/SAMPLED)

關鍵選項深度解析

1. 排序順序：ASC / DESC

• ASC (預設)：鍵值升序儲存。
• DESC：鍵值降序儲存。
• DB2 特有優勢：雙向掃描。無論定義 ASC 或 DESC，**游標均可正向/反向高效掃描**。CREATE INDEX idx ON t(c ASC) 等同於 CREATE INDEX idx ON t(c DESC) 對 ORDER BY c DESC 的支援度。**無需為正反向各建一個索引**。

2. 函數索引鍵：UCASE / LCASE

CREATE INDEX idx_name_upper ON employee (UCASE(lastname));
-- 查詢 WHERE UCASE(lastname) = 'SMITH' 可用索引

• 支援 UCASE/LCASE (或 UPPER/LOWER) 函數運算結果建索引。
• 引數必須為 CHAR/VARCHAR 類型。
• 解決「大小寫不敏感查詢」無法用索引問題。

3. 表達式索引鍵 (DB2 10.5+)

CREATE INDEX idx_salary_bonus ON emp (salary + bonus);
CREATE INDEX idx_json_field ON t (JSON_VAL(data, '$.price'));

直接對表達式/函數結果建索引，查詢含相同表達式時自動匹配。

建立規則與限制

• 欄位上限：單一索引最多 **8 個欄位** (複合索引)。
• 長度上限：單欄位 ≤ 1024 bytes；索引鍵總長度 ≤ 頁面大小減開銷 (約 4K 頁面 ~3.5KB)。
• 數量上限：
  • 無主鍵表：最多 **15 個索引**。
  • 有主鍵表：最多 **14 個索引** (主鍵佔 1 個)。
• 重複索引檢測：欄位集合、順序、排序屬性完全相同視為重複，建立失敗 (SQLSTATE 42711)。
• 禁止類型：BLOB/CLOB/DBCLOB/XML 欄位**不可直接建索引** (需用文本搜尋/ XML 索引)。

DB2 獨特優化特性

前綴掃描

複合索引 (A, B, C, D, E, F, G, K) 可直接滿足 WHERE A=1 AND B=2、WHERE A=1 AND B=2 AND C=3 等**左前綴**查詢，**無需額外建立前綴子集索引**。

雙向掃描

如前所述，單一索引支援 ORDER BY col ASC 與 ORDER BY col DESC 同等高效，無需雙索引。

髒頁索引 (Dirty Page Index)

CREATE INDEX idx_dirty ON t ($dirty);

特殊系統索引，用於追蹤緩衝池中已修改未刷盤的頁面，加速 Crash Recovery。一般用戶無需建立。

指定索引表空間

建表時分離索引與資料表空間 (提升 I/O 並行)：

CREATE TABLE t (...) IN tbsp_data INDEX IN tbsp_index;

或建立索引時 (DB2 部分版本支援)：

CREATE INDEX idx ON t(col) IN tbsp_index; -- 語法視版本而異，建表時指定最通用

實戰範例

-- 1. 標準複合索引 (最左前綴原則)
CREATE INDEX idx_emp_dept_job ON employee (workdept ASC, job ASC);

-- 2. 函數索引 (大小寫不敏感)
CREATE INDEX idx_emp_name_upper ON employee (UCASE(lastname));

-- 3. 含壓縮與統計收集 (生產建議)
CREATE INDEX idx_sales_date_region ON sales (sale_date, region_id)
COMPRESS YES
COLLECT STATISTICS DETAILED;

-- 4. 唯一索引 (隱含唯一約束)
CREATE UNIQUE INDEX idx_pk_emp ON employee (empno);

維護建議

1. 定期 RUNSTATS 更新索引統計 (NLEAF, NLEVELS, CLUSTERRATIO, FIRSTKEYCARD, FULLKEYCARD)。
2. 監控 REORGCHK 或 SYSPROC.ADMIN_GET_INDEX_INFO 判斷是否需 REORG INDEXES。
3. 利用 PCTFREE 預留插入空間 (如易熱點頁面)，減少分裂。
4. 大表考慮 **分區索引** (配合分區表) 或 **MDC 區塊索引**。