Oracle 索引實戰指南：建立策略、分區索引、聚簇替代與表索引分離存儲

索引：查詢效能的加速器，也是維護成本的來源

索引透過 B*Tree 將邏輯值映射至 ROWID，實現 O(log n) 存取。但索引非越多越好——寫入開銷、空間佔用、優化器誤判風險並存。本文提供索引建立決策框架、大表分區索引策略、聚簇替代方案、表索引物理隔離最佳實踐。

索引建立核心決策矩陣

大表索引特殊挑戰與分區索引方案

問題：大表索引佔用大量 Buffer Cache (索引塊優先級高)，擠佔資料塊，導致頻繁物理讀、效能抖動。

方案：Local Partitioned Index (本地分區索引)

• 索引分區與表分區一一對應，等分區對齊
• 分區維護 (TRUNCATE/DROP/EXCHANGE) 自動同步索引，無需 REBUILD
• 分區修剪同時作用於表與索引，雙重過濾
• Global Index 僅在跨分區唯一性約束、全域排序需求時使用

聚簇：多表共儲的替代連接方案

-- 建立索引聚簇
CREATE CLUSTER emp_dept_cluster (deptno NUMBER(2))
SIZE 1024  -- 預估每鍵值資料大小
INDEX;

CREATE INDEX idx_emp_dept_cluster ON CLUSTER emp_dept_cluster;

CREATE TABLE dept_clustered (
  deptno NUMBER(2) PRIMARY KEY,
  dname  VARCHAR2(14)
) CLUSTER emp_dept_cluster (deptno);

CREATE TABLE emp_clustered (
  empno  NUMBER(4) PRIMARY KEY,
  ename  VARCHAR2(10),
  deptno NUMBER(2) REFERENCES dept_clustered(deptno)
) CLUSTER emp_dept_cluster (deptno);

表與索引物理隔離存儲：消除 I/O 爭用

原理：表資料 I/O (順序/隨機) 與索引 I/O (隨機) 混雜同一磁碟 → 磁頭頻繁跳動、佇列擁塞。

實施：

-- 邏輯分離
CREATE TABLESPACE tbs_data  DATAFILE '+DATA'  SIZE 100G;
CREATE TABLESPACE tbs_index DATAFILE '+INDEX' SIZE 50G;

-- 建表指定
CREATE TABLE orders (...) TABLESPACE tbs_data;
CREATE INDEX idx_orders_cust ON orders(customer_id) TABLESPACE tbs_index;

-- 物理隔離 (ASM)
-- DATA Diskgroup: 普通冗余、大容量 HDD/SSD 混合
-- INDEX Diskgroup: 高效能 SSD/NVMe、小尺寸 AU (1MB)

索引維護與監控體系

進階索引類型選擇指南

索引是把雙刃劍：精準建立、分區對齊、物理隔離、持續監控、按需進階，才能在讀寫平衡點上持續獲取淨收益。