Oracle 分區表效能優化實戰：範圍、雜湊、列表、複合分區完整建模與 I/O 對比驗證

前置作業：建立獨立表空間隔離 I/O

CREATE TABLESPACE test01 DATAFILE 'D:\xtdb\test01.dbf' SIZE 50M;
CREATE TABLESPACE test02 DATAFILE 'D:\xtdb\test02.dbf' SIZE 50M;
CREATE TABLESPACE test03 DATAFILE 'D:\xtdb\test03.dbf' SIZE 50M;
CREATE TABLESPACE test04 DATAFILE 'D:\xtdb\test04.dbf' SIZE 100M; -- 非分區表對照組

建立對照表 test_emp01 於 test04，結構同 scott.emp。

一、範圍分區 — 依數值區間劃分

CREATE TABLE test01 (
  empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9),
  mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2)
)
PARTITION BY RANGE (sal)
( PARTITION sal_p1 VALUES LESS THAN (1500) TABLESPACE test01,
  PARTITION sal_p2 VALUES LESS THAN (3000) TABLESPACE test02,
  PARTITION sal_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE test03 );

灌入 688,128 筆資料 (反覆 INSERT SELECT + COMMIT)。
對照組 test_emp01 同量級資料。

I/O 對比驗證

• SELECT * FROM test_emp01 WHERE sal < 1500; → 全表掃描 test04，觀察 iostat -x 2 15 (或工作管理員) 磁碟佇列/服務時間。
• SELECT * FROM test01 PARTITION (sal_p1); → **僅掃描 test01 表空間**，I/O 顯著降低，服務時間縮短。

**結論**：分區裁剪 讓查詢只讀相關磁碟，物理 I/O 成比例下降。

二、雜湊分區 — 依雜湊值均勻分佈

CREATE TABLE test02 ( ... same columns ... )
PARTITION BY HASH (empno)
( PARTITION test02_p1 TABLESPACE test01,
  PARTITION test02_p2 TABLESPACE test02,
  PARTITION test02_p3 TABLESPACE test03 );

插入資料後查詢各分區行數，觀察三表空間負載均衡。適用於**無明顯範圍特徵、需均勻分散 I/O** 的大表。

三、列表分區 — 業務自定義離散值映射

CREATE TABLE test03 ( ... )
PARTITION BY LIST (deptno)
( PARTITION test03_p1 VALUES (10) TABLESPACE test01,
  PARTITION test03_p2 VALUES (20) TABLESPACE test02,
  PARTITION test03_p3 VALUES (30) TABLESPACE test03 );

精確控制特定部門資料落盤位置，適用於**代碼表、狀態欄位、地域編碼**等離散值場景。

四、複合分區 — 兩級劃分兼顧篩選與均勻

A. 範圍 + 雜湊 (Range-Hash)

CREATE TABLE test04 ( ... )
PARTITION BY RANGE (sal)
SUBPARTITION BY HASH (empno)
SUBPARTITIONS 3 STORE IN (test01, test02, test03)
( PARTITION sal_p1 VALUES LESS THAN (1500),
  PARTITION sal_p2 VALUES LESS THAN (3000),
  PARTITION sal_p3 VALUES LESS THAN (MAXVALUE) );

查詢 USER_TAB_SUBPARTITIONS 可見 9 個子分區 (3 Range × 3 Hash) 輪流分佈於三表空間。**優勢**：範圍裁剪定位 Partition → 雜湊均勻平行掃描 Subpartition。

B. 範圍 + 列表 (Range-List)

CREATE TABLE test05 ( ... )
PARTITION BY RANGE (sal)
SUBPARTITION BY LIST (deptno)
SUBPARTITION TEMPLATE
( SUBPARTITION p1 VALUES (10),
  SUBPARTITION p2 VALUES (20),
  SUBPARTITION p3 VALUES (30) )
( PARTITION sal_p1 VALUES LESS THAN (1500) TABLESPACE test01,
  PARTITION sal_p2 VALUES LESS THAN (3000) TABLESPACE test02,
  PARTITION sal_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE test03 );

子分區名稱可讀 (如 SAL_P2_P2 代表薪資 1500-3000 且部門 20)。驗證：插入 sal<3000, deptno=20 資料，確確實實落在 test02 表空間的 SAL_P2_P2 子分區。

分區表優化核心價值總結

1. 分區裁剪：WHERE 條件含分區鍵 → 僅掃描目標分區，I/O 線性縮減。
2. 分區級維護：TRUNCATE/DROP/EXCHANGE PARTITION 秒級完成，無 Redo/Undo 風暴。
3. 平行查詢：多子分區天然支援 PX (Parallel Execution) 掃描。
4. 存儲分級：熱分區放 SSD/NVMe，冷分區放 HDD/雲端冷存儲，成本優化。
5. 索引對齊：Local 分區索引與表分區一對一，維護零干擾。

選型決策樹

• 時間序列/數值範圍明確 → **Range**。
• 無範圍特徵、需均勻分散 → **Hash**。
• 離散代碼/業務屬性固定集合 → **List**。
• 需同時支援範圍過濾與平行掃描 → **Range-Hash**。
• 需範圍過濾 + 業務屬性精確定位 → **Range-List**。