Oracle 分區技術深度解析：支撐大數據操作的核心策略與實戰

Oracle 分區技術深度解析：支撐大數據操作的核心策略與實戰

整理自 Oracle 資深顧問羅女士《大批量數據處理技術》演講，結合實務經驗，系統闡述分區類型、索引策略、維護操作、Exchange/TTs 技術、滾動窗口與效能評估。

一、分區核心概念

• 分區：將大表/索引按欄位值分解為較小邏輯片段，各擁有獨立 Segment
• 透明性：SQL/DML 無感知，DDL 可針對單分區操作
• 優先級：高性能 → 數據維護 → 實施難度 → 高可用性

二、四大基礎分區策略

三、分區語法範例

Range 分區

CREATE TABLE graderecord (
  sno VARCHAR2(10), sname VARCHAR2(20), dormitory VARCHAR2(3), grade INT
)
PARTITION BY RANGE (grade) (
  PARTITION bujige VALUES LESS THAN (60),
  PARTITION jige   VALUES LESS THAN (85),
  PARTITION youxiu VALUES LESS THAN (MAXVALUE)
);

Hash 分區

CREATE TABLE graderecord (...)
PARTITION BY HASH (sno) (
  PARTITION p1, PARTITION p2, PARTITION p3
);

List 分區

CREATE TABLE graderecord (...)
PARTITION BY LIST (dormitory) (
  PARTITION d229 VALUES ('229'),
  PARTITION d228 VALUES ('228'),
  PARTITION d240 VALUES ('240')
);

複合分區：Range-Hash

CREATE TABLE graderecord (...)
PARTITION BY RANGE (grade)
SUBPARTITION BY HASH (sno, sname) (
  PARTITION p1 VALUES LESS THAN (75) (
    SUBPARTITION sp1, SUBPARTITION sp2
  ),
  PARTITION p2 VALUES LESS THAN (MAXVALUE) (
    SUBPARTITION sp3, SUBPARTITION sp4
  )
);

複合分區：Range-List (含模板)

CREATE TABLE MobileMessage (
  ACCT_MONTH VARCHAR2(6), AREA_NO VARCHAR2(10), DAY_ID VARCHAR2(2),
  SUBSCRBID VARCHAR2(20), SVCNUM VARCHAR2(30)
)
PARTITION BY RANGE (ACCT_MONTH, AREA_NO)
SUBPARTITION BY LIST (DAY_ID)
SUBPARTITION TEMPLATE (
  SUBPARTITION sub1 VALUES ('01'), ..., SUBPARTITION sub31 VALUES ('31')
)
( PARTITION p_0701_010 VALUES LESS THAN ('200701','011'), ... );

四、分區維護操作

五、分區索引策略

關鍵實驗：分區消除驗證

-- 建分區表 + Local Prefixed/Non-prefixed 索引
-- 將部分表空間 OFFLINE 模擬損壞
-- 查詢含分區鍵 → 成功（消除損壞分區）
-- 查詢無分區鍵 → ORA-00376/ORA-01110（掃描到損壞分區）

六、Exchange 應用三大場景

1. 全文檢索：Stage 表建 CTX 索引 → Exchange 進主表分區
2. ETL：獨立表空間載入 → TTS 傳輸 → 清洗建索引 → Exchange
3. 重複資料刪除：Exchange 出問題分區 → 刪除重複 → Exchange 回 → 重建分區索引

七、可傳輸表空間 (TTS) 技術

• 流程：來源表空間 READ ONLY → EXP TTS → FTP Datafile/DMP → IMP TTS
• 優勢：效能遠超 exp/imp、僅傳輸 Datafile、可同步索引、來源影響極小
• 應用：業務→數倉、歸檔、數倉→數據集市、對外發布、TSPITR

八、滾動窗口

• 歷史分區 DROP/ARCHIVE → 新分區 ADD → 實現固定窗口保留
• 配合 Global Index UPDATE GLOBAL INDEXES 避免 UNUSABLE

九、分區設計原則

1. 表 > 1.5-2GB 或 OLTP > 1000 萬行 → 考慮分區
2. 查詢多為近期資料 → Range 時間分區
3. 批次刪除歷史 → Range/List 分區配合 TRUNCATE/DROP
4. 備份按表空間 → 分區對應表空間
5. 大量唯讀 → 唯讀表空間免備份
6. 頻繁 PDML → 分區支援並行
7. 部分資料高可用 → 分區隔離故障

十、效能評估指標

• 響應時間、CPU/記憶體/I/O 消耗
• 工具：Oracle Trace、Autotrace、TKPROF
• 遷移/備份/擴展/高可用能力