Oracle 中實現非主鍵聚集索引效果的正確姿勢

背景釐清

原文使用 sp_helpindex、CLUSTERED INDEX 等語法，實為 SQL Server 特有功能。Oracle 不支援 獨立於表的聚集索引概念，但提供 索引組織表（IOT） 與 聚集 兩種機制達到相似效果。

方案一：索引組織表（IOT）—— 最接近聚集索引

資料直接儲存在 B-tree 索引結構中，主鍵即為儲存順序：

CREATE TABLE employee_iot (
    dept_id   NUMBER,
    emp_id    NUMBER,
    name      VARCHAR2(100),
    salary    NUMBER,
    CONSTRAINT pk_emp_iot PRIMARY KEY (dept_id, emp_id)
) ORGANIZATION INDEX
  PCTTHRESHOLD 20
  OVERFLOW TABLESPACE users;

• 依 (dept_id, emp_id) 物理排序，範圍查詢極快
• 非主鍵欄位可存入 OVERFLOW 區段
• 適合「以特定鍵值頻織範圍掃描」的場景

方案二：表聚集—— 多表共享儲存

-- 1. 建立叢集鍵
CREATE CLUSTER dept_emp_cluster (dept_id NUMBER)
  SIZE 1024 TABLESPACE users;

-- 2. 建立叢集索引
CREATE INDEX idx_dept_emp_cluster ON CLUSTER dept_emp_cluster;

-- 3. 將表放入叢集
CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, dname VARCHAR2(50))
  CLUSTER dept_emp_cluster (dept_id);

CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, dept_id NUMBER, ename VARCHAR2(100))
  CLUSTER dept_emp_cluster (dept_id);

• 相同 dept_id 的部門與員工資料物理相鄰
• Join 查詢無需額外 I/O
• 不適合高頻獨立存取單表的場景

方案三：一般堆積表 + 覆蓋索引

若無法使用 IOT/Cluster，建立包含查詢所需所有欄位的複合索引：

CREATE INDEX idx_emp_dept_cover ON emp(dept_id, emp_id, name, salary);

• Index-only scan 避免回表
• 維護成本低於 IOT
• 適用於大多數 OLTP 場景

選型建議