Oracle 表分區完整指南：範圍、雜湊、清單、複合與索引分區語法詳解

分區表優勢

• 查詢效能提升（分區剪枝）
• 資料管理彈性（獨立載入/歸檔/清理分區）
• 備份恢復粒度更細

五大分區類型語法速查

1. 範圍分區

CREATE TABLE range_sales (
  id NUMBER PRIMARY KEY,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_q1 VALUES LESS THAN (TO_DATE('2024-04-01','YYYY-MM-DD')) TABLESPACE ts_q1,
  PARTITION p_q2 VALUES LESS THAN (TO_DATE('2024-07-01','YYYY-MM-DD')) TABLESPACE ts_q2,
  PARTITION p_max VALUES LESS THAN (MAXVALUE) TABLESPACE ts_max
);

MAXVALUE 代表無上限，確保所有資料有歸屬。

2. 雜湊分區

-- 方式一：指定分區數與表空間組，循環分配
CREATE TABLE hash_sales (
  id NUMBER PRIMARY KEY,
  cust_id NUMBER,
  amount NUMBER
)
PARTITION BY HASH (cust_id)
PARTITIONS 8
STORE IN (ts_1, ts_2, ts_3, ts_4);

-- 方式二：具名分區
CREATE TABLE hash_sales2 (
  id NUMBER PRIMARY KEY,
  cust_id NUMBER
)
PARTITION BY HASH (cust_id) (
  PARTITION p1 TABLESPACE ts_1,
  PARTITION p2 TABLESPACE ts_2
);

3. 清單分區

CREATE TABLE list_sales (
  id NUMBER PRIMARY KEY,
  region VARCHAR2(20),
  amount NUMBER
)
PARTITION BY LIST (region) (
  PARTITION p_north VALUES ('北京','天津','河北') TABLESPACE ts_north,
  PARTITION p_south VALUES ('廣東','廣西','海南') TABLESPACE ts_south,
  PARTITION p_other VALUES (DEFAULT) TABLESPACE ts_other
);

明確列出每個分區對應的離散值，支援 DEFAULT 捕獲未列舉值。

4. 複合分區（子分區）

CREATE TABLE composite_sales (
  id NUMBER PRIMARY KEY,
  sale_date DATE,
  region VARCHAR2(20),
  amount NUMBER
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (region)
SUBPARTITIONS 4 (
  PARTITION p_2024_h1 VALUES LESS THAN (TO_DATE('2024-07-01','YYYY-MM-DD')) TABLESPACE ts_h1,
  PARTITION p_2024_h2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts_h2
);

外層 RANGE，內層 HASH，實現二維分區。

5. 索引分區

-- 本地索引：每個表分區對應一個索引分區，易管理
CREATE INDEX idx_sales_amount ON sales(amount)
LOCAL (
  PARTITION p_q1 TABLESPACE ts_idx_q1,
  PARTITION p_q2 TABLESPACE ts_idx_q2,
  PARTITION p_max TABLESPACE ts_idx_max
);

-- 全域索引：跨分區，查詢較快但維護複雜
CREATE INDEX idx_sales_cust ON sales(cust_id) GLOBAL;

限制： 雜湊分區與複合分區不可建立全域索引。

查詢指定分區資料

SELECT * FROM sales PARTITION (p_q1);

分區維護常用操作

• ALTER TABLE ... ADD PARTITION ... 新增分區
• ALTER TABLE ... DROP PARTITION ... 刪除分區（資料同步刪除）
• ALTER TABLE ... TRUNCATE PARTITION ... 清空分區資料（保留結構）
• ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... 交換分區與獨立表（極速載入/歸檔）
• ALTER TABLE ... MOVE PARTITION ... TABLESPACE ... 移動分區表空間
• ALTER INDEX ... REBUILD PARTITION ... 重建索引分區

效能調優建議

1. 分區鍵選擇：優先用 WHERE 條件高頻欄位（日期、區域、客戶 ID）。
2. 分區數量：避免過多（>1000）導致字典膨脹，亦避免過少失去剪枝效益。
3. 配合本地索引：等分區維護、平行查詢友善。
4. 統計資訊：分區層級收集統計，DBMS_STATS.GATHER_TABLE_STATS(..., GRANULARITY=>'PARTITION')。