Oracle 索引建立三大黃金準則：適當的表、適當的欄位、適當的數量

前言：索引是雙面刃，精準才是王道

「在適當的表、適當的欄位上建立適當數量的索引」——這句口訣易記卻難行。濫建索引會拖垮寫入效能、佔用儲存、干擾優化器；欠建則導致全表掃描、應用超時。本文從表級決策、欄位特徵識別、數量平衡點三個維度，提供可量化、可驗證的判斷框架。

準則一：適當的表 —— 以「掃描成本 vs 索引維護成本」決策

1. 小表：通常不需要索引

• 閾值：資料塊 < 1000（約 < 8MB）或列數 < 10000 筆
• 理由：全表掃描單次多區塊讀取（MBRC）極快，索引存取反而需「索引範圍掃描 + TABLE ACCESS BY INDEX ROWID」雙重 I/O
• 例外：強制唯一性約束（PK/UK）、外鍵欄位（避免父表鎖）、極高頻單筆點查

2. 大表：不一定要建索引

判斷公式：「常用查詢回傳比例」 = 典型 WHERE 條件回傳行數 / 表總行數

• < 1%-5%：必建索引，索引範圍掃描遠快於全表掃描
• 5%-15%：灰區，需實測對比（見下文測試法）
• > 15%-20%：通常不建，全表掃描更優；優化器若誤選索引，用 /*+ FULL(t) */ 或統計資訊修正

3. 實測驗證法（灰區決策）

-- 1. 收集統計資訊
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'BIG_TABLE', CASCADE=>TRUE);

-- 2. 取得典型查詢計畫
EXPLAIN PLAN FOR SELECT /*+ INDEX(big_table idx_col) */ * FROM big_table WHERE col = :val;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 3. 強制全表掃描對比
EXPLAIN PLAN FOR SELECT /*+ FULL(big_table) */ * FROM big_table WHERE col = :val;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 4. 實際執行統計（需 Enterprise Edition）
-- 啟用 SQL Trace 或使用 DBMS_SQLTUNE 對比 Elapsed Time、Buffer Gets、Disk Reads

決策：若索引計畫 Cost 明顯低於全表掃描，且實測一致，則建立索引。

4. 特殊表類型

準則二：適當的欄位 —— 以「選擇率」與「查詢模式」識別

1. 高選擇率欄位（優先建立）

• 定義：DISTINCT 個數 / 總行數 > 0.9（接近唯一）
• 典型：PK、UK、身分證號、手機號、訂單號、交易流水號
• 效果：索引範圍掃描極窄，回表行數極少，Cost 極低

2. 低選擇率但高頻查詢的欄位（謹慎評估）

• 典型：性別、狀態碼、類型碼、是否刪除旗標
• 策略：
  • 單獨建 B-tree 索引 通常無效（優化器判斷回表成本過高而選全表掃描）
  • 複合索引前置欄位：CREATE INDEX idx_order_cust_status ON orders(customer_id, status); 先高選擇率後低選擇率
  • 位圖索引：僅限資料倉庫/低併發環境，CREATE BITMAP INDEX idx_order_status ON orders(status);
  • 部分索引 (12c+)：CREATE INDEX idx_active_orders ON orders(status) INDEXING PARTIAL; 僅索引 status='ACTIVE' 的分區

3. 連接鍵欄位（必建）

• 所有 JOIN ON 條件欄位、外鍵欄位
• 避免 HASH JOIN 暴力掃描、NESTED LOOPS 驅動表無索引導致全表掃描
• 外鍵索引同時防止父表 DML 鎖定子表全表（TM 模式 4/5）

4. 排序/分組欄位（支援 Top-N）

-- 查詢：SELECT * FROM orders WHERE cust_id=:cid ORDER BY order_date DESC FETCH FIRST 10 ROWS ONLY
-- 最佳索引：CREATE INDEX idx_order_cust_dt ON orders(cust_id, order_date DESC);
-- 避免：FILESORT、全表掃描後排序

5. 函數/運算式查詢欄位（函數索引）

-- 查詢：WHERE UPPER(email) = 'USER@EXAMPLE.COM'
CREATE INDEX idx_user_upper_email ON users(UPPER(email));

-- 查詢：WHERE SUBSTR(id_no,1,1) = 'A'
CREATE INDEX idx_id_first_char ON users(SUBSTR(id_no,1,1));

查詢必須**完全匹配**索引定義的運算式，否則不會使用。

6. NULL 值處理

• B-tree 索引**不儲存全 NULL 鍵值**的行
• WHERE col IS NULL 無法使用單欄 B-tree 索引
• 解法：CREATE INDEX idx_col_not_null ON users(CASE WHEN col IS NULL THEN 1 END); 或複合索引加常數 CREATE INDEX idx_col_const ON users(col, 1);

準則三：適當的數量 —— 在「查詢加速」與「寫入負擔」間平衡

1. 寫入負擔量化

每個索引約增加 1.5-3 倍 的 DML 邏輯 I/O（視索引高度、分裂頻率）。

-- 監控表級 DML 與索引維護比例
SELECT segment_name, logical_reads, physical_reads, db_block_changes
FROM v$segment_statistics
WHERE owner = 'SCHEMA' AND statistic_name IN ('logical reads','physical reads','db block changes')
ORDER BY db_block_changes DESC;

2. 系統類型決策表

3. 索引生命週期管理

1. 定期巡檢：DBA_INDEX_USAGE (19c+) 或 V$OBJECT_USAGE (監控模式) 找出長期未用索引
2. 碎片評估：ANALYZE INDEX ... VALIDATE STRUCTURE 檢查 HEIGHT、LF_ROWS/DEL_LF_ROWS 比例
3. 重建策略：ALTER INDEX ... REBUILD ONLINE（企業版）、COALESCE（輕量合併葉節點）
4. 刪除流程：ALTER INDEX ... UNUSABLE 觀察 1 週 → 確無影響 → DROP INDEX

綜合決策流程圖

1. 識別核心查詢（Top 20 SQL by Elapsed Time / Executions）
2. 分析 WHERE/JOIN/ORDER BY/GROUP BY 欄位組合
3. 計算各欄位選擇率、NULL 比例、資料型別
4. 設計候選索引集（單欄、複合、函數、部分）
5. 測試環境負載對比（AWR/STS 回放）
6. 上線後持續監控 INDEX_USAGE、DML 延遲、空間增長
7. 季度審視：刪除、合併、調整欄位順序

索引優化沒有終點，只有持續的「觀測-假設-驗證」循環。記住：最好的索引是「剛好夠用」的索引。