Oracle 索引建立語法大全：唯一、組合、反向、位圖、函數式

前言

索引是提升查詢效能的關鍵結構，但建立索引需權衡寫入開銷。Oracle 提供五大類索引，適用不同場景。本文整理標準建立語法與適用時機。

基本語法骨架

CREATE [UNIQUE] INDEX index_name ON table_name (col1, col2, ...)
[TABLESPACE tablespace_name]
[REVERSE] -- 僅適用於 B-tree 索引
[INDEXTYPE IS ctxsys.context ...] -- 專用索引類型 (如全文、空間)

五大索引類型建立範例

1. 唯一索引

強制欄位值唯一 (含單一 NULL)，常用於業務鍵 (如 Email、身分證字號)。

CREATE UNIQUE INDEX idx_users_email ON users(email) TABLESPACE idx_ts;

2. 組合索引 (複合索引)

多欄位組合，順序決定選擇性，**最左前綴原則**：WHERE 條件需包含前導欄位才能用到索引。

CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date) TABLESPACE idx_ts;

3. 反向鍵索引

將索引鍵值位元組反轉儲存，分散熱點區塊，適用於**遞增序列主鍵**的高併發 INSERT 場景 (減少緩衝區忙碌等待)。

CREATE INDEX idx_rev_pk ON big_table(id) REVERSE TABLESPACE idx_ts;

4. 位圖索引

針對**低基數**欄位 (如性別、狀態、地區)，以位圖儲存 ROWID，支援高效的 AND/OR 多條件過濾，**嚴禁用於高併發 OLTP 寫入環境** (鎖定粒度大)。

CREATE BITMAP INDEX idx_orders_status ON orders(status) TABLESPACE idx_ts;

5. 函數式索引

對運算式結果建立索引，解決 WHERE 條件使用函數導致無法用到索引的問題。

CREATE INDEX idx_upper_name ON users(UPPER(name)) TABLESPACE idx_ts;
-- 查詢 WHERE UPPER(name) = 'JOHN' 即可使用

建立索引前的檢核清單

• 欄位是否常出現在 WHERE、JOIN、ORDER BY 條件？
• 基數 (Distinct Values) 是否足夠高？(位圖索引除外)
• 表大小：小表 (千筆內) 全表掃描可能更快，無需索引。
• DML 頻率：頻繁 INSERT/UPDATE 的表，索引過多會拖慢寫入。
• 指定 TABLESPACE 將索引與資料分離不同磁碟，提升 I/O 平行度。