Oracle 索引建立語法大全：唯一、組合、反轉鍵、位圖、函數基 5 類索引建立範例

Oracle 索引建立語法大全：5 類索引建立範例與適用場景

適當索引能大幅提升查詢效能。本文整理 5 種常見索引的標準建立語法、關鍵字與適用場景速查。

標準建立語法骨架

CREATE [UNIQUE|BITMAP] INDEX index_name
ON table_name (col1, col2, ...)
[TABLESPACE ts_name]
[REVERSE]           -- 反轉鍵
[COMPRESS n]        -- 壓縮前導 n 欄
[LOCAL|GLOBAL ...]  -- 分區索引
[PARALLEL n];       -- 並行建立

五類索引建立範例

進階選項補充

• COMPRESS 2：壓縮組合索引前 2 欄，適合前導欄重複度高。
• PARALLEL 4：大表建立索引時平行化，建立後記得 NOPARALLEL。
• ONLINE：11g+ 支援線上建立/重建，DML 不阻塞（需 Enterprise Edition）。
• INVISIBLE：11g+ 建立隱形索引，優化器忽略，測試效能影響再轉可見。

建立後驗證

-- 確認索引狀態
SELECT index_name, index_type, uniqueness, status, compression
FROM user_indexes WHERE table_name = 'T';

-- 查看執行計畫是否使用
EXPLAIN PLAN FOR SELECT * FROM t WHERE c1 = :b1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

避坑提醒

1. 過度索引：每個索引增加 INSERT/UPDATE/DELETE 成本，單表建議 ≤ 5-6 個。
2. 位圖索引禁用於 OLTP：DML 鎖定整個值範圍 ROWID，易死結。
3. 函數基索引需收集統計資訊：DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL HIDDEN COLUMNS')。
4. 反轉鍵索引不可用於範圍查詢、不支援位圖/IOT。