Oracle 大表分區管理實戰：簡化維護、提升效能、平衡 I/O 的分區策略

分區核心價值

Oracle 8 推出分區選項，將大表水平切分至多個表空間，賦予物理層面可管理性。六大核心優勢：

1. 增強可用性：單分區故障不影響其他分區存取
2. 減少停機時間：僅修復受損分區，非整表重建
3. 維護輕鬆：獨立管理分區 (Truncate/Rebuild/Compress) 替代整表操作
4. 平衡 I/O：分區分佈不同磁碟/ASM 磁碟組，平行 I/O
5. 提升效能：分區剪枝 + 平行查詢/ DML，大幅加速
6. 對應用透明：SQL 無感知，維護零代碼變更

分區類型選擇指南

實戰案例：銷售記錄按季度 RANGE 分區

場景：每月產生 30MB，1999 年資料按季度分四個分區，各配 100MB 表空間。

步驟 1：建立分區表空間

CREATE TABLESPACE ts_sale1999q1
DATAFILE '/u1/oradata/sales/sales1999_q1.dat' SIZE 100M
DEFAULT STORAGE (INITIAL 30M NEXT 30M MINEXTENTS 3 PCTINCREASE 0);

CREATE TABLESPACE ts_sale1999q2
DATAFILE '/u1/oradata/sales/sales1999_q2.dat' SIZE 100M
DEFAULT STORAGE (INITIAL 30M NEXT 30M MINEXTENTS 3 PCTINCREASE 0);

CREATE TABLESPACE ts_sale1999q3
DATAFILE '/u1/oradata/sales/sales1999_q3.dat' SIZE 100M
DEFAULT STORAGE (INITIAL 30M NEXT 30M MINEXTENTS 3 PCTINCREASE 0);

CREATE TABLESPACE ts_sale1999q4
DATAFILE '/u1/oradata/sales/sales1999_q4.dat' SIZE 100M
DEFAULT STORAGE (INITIAL 30M NEXT 30M MINEXTENTS 3 PCTINCREASE 0);

步驟 2：建立分區表

CREATE TABLE sales (
  invoice_no  NUMBER,
  ...,
  sale_date   DATE NOT NULL
)
PARTITION BY RANGE (sale_date) (
  PARTITION sales1999_q1 VALUES LESS THAN (TO_DATE('1999-04-01','YYYY-MM-DD')) TABLESPACE ts_sale1999q1,
  PARTITION sales1999_q2 VALUES LESS THAN (TO_DATE('1999-07-01','YYYY-MM-DD')) TABLESPACE ts_sale1999q2,
  PARTITION sales1999_q3 VALUES LESS THAN (TO_DATE('1999-10-01','YYYY-MM-DD')) TABLESPACE ts_sale1999q3,
  PARTITION sales1999_q4 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')) TABLESPACE ts_sale1999q4
);

進階分區運維操作

新增分區 (滾動窗口)

-- Range 表新增
ALTER TABLE sales ADD PARTITION sales2000_q1
VALUES LESS THAN (TO_DATE('2000-04-01','YYYY-MM-DD')) TABLESPACE ts_sale2000q1;

-- Interval 表自動新增，無需手動

分區截斷 (歸檔/清理歷史)

ALTER TABLE sales TRUNCATE PARTITION sales1999_q1;
-- 瞬間釋放空間，極速完成，生成最少 REDO

分區交換 (ETL 高效載入)

-- 1. 建立臨時表結構完全一致
CREATE TABLE sales_staging ... TABLESPACE staging_ts;

-- 2. 載入資料至臨時表 (可平行、無索引、無約束)

-- 3. 瞬間交換 (元資料操作，零資料移動)
ALTER TABLE sales EXCHANGE PARTITION sales2000_q1 WITH TABLE sales_staging
INCLUDING INDEXES WITHOUT VALIDATION;

分區合併/分割/移動

-- 合併相鄰分區
ALTER TABLE sales MERGE PARTITIONS sales1999_q3, sales1999_q4
INTO PARTITION sales1999_h2 TABLESPACE ts_sale1999h2;

-- 分割大分區
ALTER TABLE sales SPLIT PARTITION sales2000_q1 AT (TO_DATE('2000-02-15','YYYY-MM-DD'))
INTO (PARTITION sales2000_q1_1 TABLESPACE ts1, PARTITION sales2000_q1_2 TABLESPACE ts2);

-- 移動分區至新表空間 (線上可用 DBMS_REDEFINITION)
ALTER TABLE sales MOVE PARTITION sales1999_q1 TABLESPACE new_ts ONLINE;

區域索引 — 分區效能關鍵

-- 本地索引：與表分區一一對應，維護最靈活
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

-- 全域索引：跨分區，維護較重，適合高基數唯一鍵
CREATE INDEX idx_sales_invoice ON sales(invoice_no) GLOBAL
PARTITION BY HASH (invoice_no) PARTITIONS 8;

分區剪枝驗證

EXPLAIN PLAN FOR
SELECT * FROM sales WHERE sale_date BETWEEN '1999-05-01' AND '1999-06-30';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 確認 PSTART/PSTOP 顯示 KEY/KEY 或具體分區號，而非 1/4

社保/電信大表分區建議

• 按月/日 RANGE/INTERVAL：配合資料保留週期 (如 3 年熱、5 年溫、永久冷)
• 複合 Range-Hash：時間範圍 + 雜湊子分區，平行度可控
• 分區表空間分離：熱資料 SSD/高效能 ASM，冷資料 HDD/低成本 ASM
• 壓縮歷史分區：ALTER TABLE ... MOVE PARTITION ... COMPRESS FOR QUERY HIGH
• 資訊生命週期管理 (ILM)：自動化分區移動/壓縮/歸檔策略

分區將「大表難題」轉化為「分區常規操作」。精準選型、自動化滾動、交換載入、本地索引、ILM 落地，讓 TB/PB 級大表同樣輕鬆可控。