Oracle 索引聚簇表原理與創建實戰：EMP/DEPT 關聯查詢優化利器

前言

當兩張表（如 EMP、DEPT）頻繁 JOIN 且按同一鍵值（DEPTNO）訪問時，將其物理存儲在同一數據塊中可大幅減少 I/O。索引聚簇表正是解決此類場景的利器。本文深度解析其工作原理，並演示從創建聚簇、聚簇索引到建表載數據的完整流程。

一、工作原理：聚簇如何減少 I/O

• 普通堆表：EMP、DEPT 分屬不同 Segment，甚至不同 Tablespace，數據塊分離。JOIN 時需分別讀取兩表塊，I/O 雙倍。
• 索引聚簇表：以聚簇鍵（DEPTNO）為依據，將同一鍵值的 EMP 行與 DEPT 行存入同一數據塊（或鏈接塊）。查詢「部門 10 的員工及部門信息」時，僅需讀取該塊，I/O 極小。
• 聚簇鍵 vs 索引鍵：索引鍵指向單行；聚簇鍵指向一個數據塊（包含該鍵值的所有行）。

二、完整創建流程（Scott 示例）

1. 創建聚簇

CONN scott/tiger;
CREATE CLUSTER emp_dept_cluster (deptno NUMBER(2))
SIZE 1024;

SIZE 參數：預估每個聚簇鍵值關聯的數據量（位元組）。假設塊大小 8KB，SIZE=1024 → 每塊最多 7 個鍵值（10,20,30,40,50,60,70）。第 8 個鍵值（80）將插入新塊。SIZE 過大浪費空間，過小導致鏈接行增加，需根據業務調優。

2. 創建聚簇索引（必須先於建表）

CREATE INDEX emp_dept_cluster_idx ON CLUSTER emp_dept_cluster;

聚簇索引映射：鍵值 → 塊地址。查詢時先走聚簇索引定位塊，再讀取塊內所有行。

3. 創建聚簇表

CONN segment_study/liugao;

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

CREATE TABLE emp (
  empno    NUMBER PRIMARY KEY,
  ename    VARCHAR2(10),
  job      VARCHAR2(9),
  mgr      NUMBER,
  hiredate DATE,
  sal      NUMBER,
  comm     NUMBER,
  deptno   NUMBER(2) CONSTRAINT emp_fk REFERENCES dept(deptno)
) CLUSTER emp_dept_cluster(deptno);

4. 驗證創建結果

SELECT cluster_name, table_name
FROM user_tables
WHERE cluster_name IS NOT NULL
ORDER BY 1;

CLUSTER_NAME      TABLE_NAME
----------------  ----------
EMP_DEPT_CLUSTER  DEPT
EMP_DEPT_CLUSTER  EMP

三、關鍵參數調優建議

四、適用與不適用場景

• ✅ 適用：頻繁 JOIN、按聚簇鍵範圍訪問、鍵值數據量適中（單塊可容納）。
• ❌ 不適用：全表掃描為主、鍵值數據量極大（超過塊大小導致大量鏈接）、頻繁 UPDATE 聚簇鍵值（行遷移開銷大）。

總結

索引聚簇表通過「物理共存」消除 JOIN I/O，是特定訪問模式下的利器。掌握 CREATE CLUSTER → CREATE INDEX ON CLUSTER → CREATE TABLE ... CLUSTER 三步曲，並精準設置 SIZE，即可發揮最大效能。