索引是查詢效能的倍增器，但「索引越多越好」是危險迷思。本文基於基數、查詢模式、DML 頻率、空間成本四大維度，建立系統化索引選型框架，涵蓋 B-Tree、Bitmap、Function-Based、Composite、Invisible、Partial 等進階類型應用場景。
| 索引類型 | 適用基數 | 典型查詢模式 | DML 影響 | 空間開銷 | 核心限制 |
|---|---|---|---|---|---|
| B-Tree (標準) | 高 (接近唯一) | =, >, <, BETWEEN, LIKE 'prefix%' | 中 | 基準 | 低基數列效能差，NULL 不索引 |
| Bitmap | 極低 (< 100 不同值) | 多條件 AND/OR 組合、星狀查詢 | **極大** (鎖定整個 Bitmap 區塊) | 極小 (壓縮) | **嚴禁 OLTP 高併發 DML**，僅限 DW/批次載入 |
| Function-Based (FBI) | 視函數結果分布 | WHERE UPPER(name)='ABC', WHERE TRUNC(dt)=:d | 同 B-Tree | 同 B-Tree | 需收集表統計 + 隱藏虛擬欄統計 |
| Composite (複合) | 視前導欄基數 | 多欄位組合過濾、排序、分組 | 中 | 較單欄大 | 前導欄原則：最常用於等值過濾、基數最高者在前 |
| Invisible (11g+) | 同底層類型 | 測試索引移除影響、軟性下線 | 維護成本同在 | 同在 | 優化器忽略，除非設定 OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE |
| Partial (12c+) | 同底層類型 | 分區表部分分區索引 | 降低維護範圍 | 節省空間 | 僅支援 Local 分區索引，查詢跨分區需注意 |
status='A' AND region='N' OR type='X'。B-Tree **不儲存全 NULL 列**。查詢 WHERE id_card IS NULL 會全表掃描。
CREATE BITMAP INDEX idx_null ON emp(id_card); (DW 適用)。CREATE INDEX idx_fbi_null ON emp(NVL(id_card,'NULL')); (OLTP 適用)，查詢改 WHERE NVL(id_card,'NULL')='NULL'。每個索引 = 每次 DML 額外的「寫入放大」。
Invisible Index 驗證移除影響：ALTER INDEX idx INVISIBLE; 觀察一週無計畫變化再 DROP。-- 大小寫不敏感查詢
CREATE INDEX idx_emp_name_upper ON emp(UPPER(ename));
-- 查詢: WHERE UPPER(ename) = 'SMITH'
-- 日期截取 (避免 TRUNC(dt) 導致索引失效)
CREATE INDEX idx_order_dt_trunc ON orders(TRUNC(order_dt));
-- 查詢: WHERE TRUNC(order_dt) = DATE '2024-01-15'
-- JSON/XML 虛擬欄索引 (12c+)
ALTER TABLE orders ADD (order_total_vc NUMBER GENERATED ALWAYS AS (JSON_VALUE(data, '$.total' RETURNING NUMBER)));
CREATE INDEX idx_order_total ON orders(order_total_vc);關鍵：FBI 建立後 **必須收集統計資訊** (含隱藏虛擬欄)，否則優化器無法正確評估基數。
-- 1. 找出長期未使用索引 (11g+ 需啟用監控)
ALTER INDEX idx_name MONITORING USAGE;
-- 一段時間後
SELECT * FROM v$object_usage WHERE used = 'NO';
-- 2. 重複/包含關係索引
-- idx(a,b) 包含 idx(a) -> 後者可刪除
-- 3. 碎片化嚴重索引 (刪除葉列 > 20%)
ANALYZE INDEX idx VALIDATE STRUCTURE;
SELECT name, del_lf_rows/lf_rows*100 pct_del FROM index_stats WHERE pct_del > 20;
-- 4. 空間浪費索引 (Leaf blocks 遠大於理論值)
SELECT index_name, leaf_blocks, blevel, distinct_keys
FROM user_indexes
WHERE leaf_blocks > distinct_keys * 1.5; -- 經驗值
