您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 表分區全維度解析：範圍、哈希、列表、組合、索引分區語法與實戰

編輯：Oracle 資料庫基礎

前言：分區表的核心價值

Oracle 分區表將大表劃分為獨立管理的分區，實現查詢分區裁剪 (Partition Pruning)、分區級維護 (Truncate/Exchange/Move)、並行度提升。本文系統講解五大分區策略語法、子分區組合、索引分區選型與查詢技巧。

一、五大基礎分區策略

1. 範圍分區 — 最經典、最常用

CREATE TABLE range_sales (
  sale_id   NUMBER PRIMARY KEY,
  sale_date DATE,
  amount    NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01','YYYY-MM-DD')) TABLESPACE ts_q1,
  PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01','YYYY-MM-DD')) TABLESPACE ts_q2,
  PARTITION p_2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01','YYYY-MM-DD')) TABLESPACE ts_q3,
  PARTITION p_2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')) TABLESPACE ts_q4,
  PARTITION p_max   VALUES LESS THAN (MAXVALUE) TABLESPACE ts_archive  -- 必須有 MAXVALUE 兜底
);

適用：時間序列、數值連續範圍 (訂單日期、交易金額區間)。
核心：VALUES LESS THAN 定義上邊界，MAXVALUE 必須作為最後一個分區。

2. 哈希分區 — 數據均勻分佈、消除熱點

-- 方式 A：指定分區數 + 表空間組 (循環映射)
CREATE TABLE hash_users (
  user_id NUMBER PRIMARY KEY,
  name    VARCHAR2(50)
)
PARTITION BY HASH (user_id)
PARTITIONS 16
STORE IN (ts_hash_1, ts_hash_2, ts_hash_3, ts_hash_4);

-- 方式 B：顯式命名每個分區 (精確控制)
CREATE TABLE hash_users_named (
  user_id NUMBER PRIMARY KEY
)
PARTITION BY HASH (user_id) (
  PARTITION p1 TABLESPACE ts_1,
  PARTITION p2 TABLESPACE ts_2,
  ...
  PARTITION p16 TABLESPACE ts_16
);

適用：無明顯範圍特徵、需均勻 I/O 分佈 (用戶 ID、設備 ID)。
注意：分區數建議為 2 的冪次 (4/8/16/32) 以均衡哈希分佈。

3. 列表分區 — 離散值精確映射

CREATE TABLE list_regions (
  order_id  NUMBER PRIMARY KEY,
  region    VARCHAR2(20)
)
PARTITION BY LIST (region) (
  PARTITION p_north VALUES ('BEIJING','TIANJIN','HEBEI') TABLESPACE ts_north,
  PARTITION p_south VALUES ('GUANGDONG','GUANGXI','HAINAN') TABLESPACE ts_south,
  PARTITION p_east  VALUES ('SHANGHAI','JIANGSU','ZHEJIANG') TABLESPACE ts_east,
  PARTITION p_west  VALUES ('SICHUAN','YUNNAN','GUizHOU') TABLESPACE ts_west,
  PARTITION p_other VALUES (DEFAULT) TABLESPACE ts_other  -- 11g+ 支持 DEFAULT 分區
);

適用：枚舉值明確、業務按區域/部門/狀態劃分。
優勢：精確控制數據歸屬，避免範圍分區「間隙值」問題。

4. 組合分區 — 兩級分區策略疊加

-- Range-Hash: 按月範圍分區，每月內按 user_id 哈希子分區
CREATE TABLE composite_sales (
  sale_id   NUMBER,
  sale_date DATE,
  user_id   NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (user_id)
SUBPARTITIONS 8  -- 每個範圍分區下 8 個哈希子分區
(
  PARTITION p_2023_01 VALUES LESS THAN (DATE '2023-02-01') TABLESPACE ts_2023_01,
  PARTITION p_2023_02 VALUES LESS THAN (DATE '2023-03-01') TABLESPACE ts_2023_02,
  PARTITION p_max     VALUES LESS THAN (MAXVALUE) TABLESPACE ts_archive
);

組合類型：Range-Hash、Range-List、List-Hash、List-Range (12c+)
核心價值：大分區維護 (按月歸檔) + 小分區並行查詢/負載均衡雙收益。

5. 間隔分區 — 11g+ 自動擴展神器

CREATE TABLE interval_sales (
  sale_id   NUMBER,
  sale_date DATE
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))  -- 自動按月創建新分區
(
  PARTITION p_2023_01 VALUES LESS THAN (DATE '2023-02-01')
);

無需預創建分區，數據插入自動按間隔生成分區，消除「ORA-14400: partition key maps to no partition」錯誤。

二、索引分區策略：Local vs Global

類型	語法關鍵字	結構	優點	缺點	適用場景
局部索引	LOCAL	每個表分區對應一個索引分區，一一映射	分區維護自動同步 (Truncate/Drop/Exchange 不失效)、易管理	跨分區查詢需掃描多個索引分區	OLTP、數據倉庫、大多數分區表首選
全局索引	GLOBAL	單一索引覆蓋所有分區，可自定義分區鍵	唯一性約束強制、跨分區點查快	分區維護導致索引 UNUSABLE 需重建、管理複雜	需全局唯一鍵、高頻點查主鍵非分區鍵

-- 局部索引 (推薦)
CREATE INDEX idx_sales_date_local ON range_sales(sale_date) LOCAL;

-- 全局索引 (可分區)
CREATE INDEX idx_sales_user_global ON range_sales(user_id) GLOBAL
PARTITION BY HASH (user_id) PARTITIONS 8;

-- 全局非分區索引
CREATE INDEX idx_sales_amount_global ON range_sales(amount) GLOBAL;

限制：哈希分區表不能創建全局非分區索引 (12c+ 放寬)，組合分區表全局索引定義複雜，建議優先 Local。

三、分區查詢與維護實用技巧

1. 查詢指定分區

SELECT * FROM range_sales PARTITION (p_2023_q1);  -- 僅掃描該分區
SELECT * FROM range_sales PARTITION FOR (DATE '2023-05-15'); -- 11g+ 自動定位分區

2. 分區維護常用 DDL

ALTER TABLE ... ADD PARTITION (範圍/列表)
ALTER TABLE ... DROP/TRUNCATE PARTITION (瞬間釋放空間，Local 索引自動同步)
ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... (數據快速裝載/卸載，無數據移動)
ALTER TABLE ... MOVE PARTITION ... TABLESPACE ... (遷移表空間，ONLINE 12.2+)
ALTER TABLE ... SPLIT/MERGE PARTITION (拆分/合併分區)

四、選型決策樹

有明顯時間/數值範圍特徵？ → Range / Interval
無範圍特徵、需均勻分佈？ → Hash
離散枚舉值 (地區/狀態/類型)？ → List
需兩級策略 (如按月範圍 + 按用戶哈希)？ → Composite (Range-Hash)
索引選擇：默認 Local，唯有全局唯一約束或非分區鍵高頻點查才考慮 Global。

五、總結

分區表是 Oracle 大數據處理的基石。掌握 Range/Hash/List/Composite/Interval 五大策略語法、Local vs Global 索引 權衡、分區維護 DDL 實戰，即可應對 TB 級表的查詢優化、歸檔清理、滾動加載等核心場景。記住：分區鍵選擇決定成敗，業務查詢模式決定分區鍵。