Stories

Detail Return Return

技術分享 | Oracle 大表改造方案 - Stories Detail

本文為墨天輪數據庫管理服務團隊第86期技術分享，內容原創，作者為技術顧問蘭珊，如需轉載請聯繫小墨（VX：modb666）並註明來源。如需查看更多文章可關注【墨天輪】公眾號。

一、報告概述 

當單表數據量增長至千萬級甚至更大規模時，數據庫系統往往會面臨顯著的性能瓶頸與運維挑戰：

  • 數據訪問的性能急劇下降，全表掃描耗時隨數據量指數級增長,索引訪問也難以降低範圍掃描的IO消耗；
  • 系統維護難度陡增，數據歸檔、備份及DDL操作（如添加字段、分區調整）因數據規模膨脹而耗時數倍增長，甚至引發長時間鎖表阻塞業務；
  • 資源爭用問題凸顯，高併發場景下I/O吞吐量達到甚至超過磁盤或網絡帶寬上限，同時大表操作導致的鎖競爭（如行鎖升級為表鎖）進一步加劇系統延遲。

針對此類問題，分區表技術通過將數據按特定規則（如範圍、哈希、列表）分片存儲到不同物理段，可有效分散數據訪問負載、加速數據歸檔與備份、減少鎖衝突範圍，從而在保證業務連續性的同時提升系統可擴展性。

二、常用分區類型

分區類型 適用場景 示例
範圍分區 時間序列數據（如訂單、日誌） PARTITION BY RANGE (order_date)
列表分區 離散值分類（如地區、狀態） PARTITION BY LIST (region)
哈希分區 均勻分佈數據，減少熱點 PARTITION BY HASH (user_id)
複合分區 多級分區（如先範圍再哈希） PARTITION BY RANGE (date) SUBPARTITION BY HASH (id)

 三、實施步驟

3.1.表數據評估

  1. 分析數據分佈：統計時間字段範圍、高頻查詢條件、數據增長趨勢。
  2. 選擇分區鍵：優先選擇 高頻查詢條件字段或者數據傾斜比較明顯的字段。
  3. 確定分區粒度：例如按天、月分區，需平衡分區數量與單分區數據量（建議單分區不超過 5000 萬行）。

3.2.創建分區表

示例：按時間範圍分區（按月分區）

  CREATE TABLE sales_partitioned (
    sale_id      NUMBER,
    sale_date    DATE,
    customer_id  NUMBER,
    amount       NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))  -- 自動按每月創建新分區
(
    PARTITION p_initial VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);

3.3.表數據遷移

當基於表數據情況、業務特徵等信息對分區表進行設計後，下一步就需要將原表中的數據遷移至新的分區表，根據數據量、環境情況、業務要求等不同，表數據遷移可以採取如下幾種方案：

方案一：在線重定義

1. 驗證表可重定義
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA_NAME', 'ORIG_TABLE');
2. 開始重定義
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => 'SCHEMA_NAME',
    orig_table   => 'ORIG_TABLE',
    int_table    => 'INTERIM_TABLE',
    options_flag => DBMS_REDEFINITION.CONS_USE_ROWID
  );
END;
3. 同步數據
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCHEMA_NAME', 'ORIG_TABLE', 'INTERIM_TABLE');
4. 完成重定義
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA_NAME', 'ORIG_TABLE', 'INTERIM_TABLE');

方案二：分區交換（適合歷史數據遷移）

1. 創建臨時表（與原表結構相同）
CREATE TABLE tmp_sales AS SELECT * FROM sales WHERE 1=0;
2. 遷移數據到臨時表
INSERT /*+ APPEND */ INTO tmp_sales SELECT * FROM sales WHERE sale_date < DATE '2023-01-01';
3. 交換分區
ALTER TABLE sales_partitioned 
EXCHANGE PARTITION p_initial WITH TABLE tmp_sales 
WITHOUT VALIDATION;

方案三：數據泵導出導入

1. 使用數據泵導出
  expdp username/password@database 
  tables=SCHEMA_NAME.ORIGINAL_TABLE_NAME 
  directory=DATA_PUMP_DIR 
  dumpfile=data_only_export.dmp 
  logfile=data_only_export.log
  content=DATA_ONLY
2. rename舊錶
  ALTER TABLE old_table_name RENAME TO new_table_name;
3. 創建新分區表
  CREATE TABLE sales_partitioned (
    sale_id      NUMBER,
    sale_date    DATE,
    customer_id  NUMBER,
    amount       NUMBER
)
PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))  -- 自動按每月創建新分區
(
    PARTITION p_initial VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
);
4. 使用數據泵導入
  impdp scott/tiger@database
  directory=DATA_PUMP_DIR 
  dumpfile=emp_data.dmp 
  logfile=emp_data_imp.log 
  content=DATA_ONLY

四、分區改造後優化要點

4.1.表索引重建

分區表上往往存在索引，但不同的索引類型在分區表改造時的表現也各有不同，需要根據業務特性、維護需求、環境等情況選擇適合特定分區表的索引類型。常見的分區索引類型包括：全局索引、本地索引，這些索引的特性如下。

1、全局索引：全表唯一性約束，但維護成本高。

語法：

CREATE INDEX index_name 
ON table_name (column_list)
GLOBAL PARTITION BY 
  RANGE(column_list) | 
  HASH(column_list) [PARTITIONS n] |
  LIST(column_list)
(
  PARTITION partition_name VALUES LESS THAN (value) [TABLESPACE tablespace_name],
  PARTITION partition_name VALUES LESS THAN (value) [TABLESPACE tablespace_name],
  ...
  PARTITION partition_name VALUES LESS THAN (MAXVALUE) [TABLESPACE tablespace_name]
)
[PARALLEL n];

示例

1）範圍分區的全局索引

CREATE INDEX idx_sales_global ON sales(sale_id)
GLOBAL PARTITION BY RANGE (sale_id)
(
  PARTITION p1 VALUES LESS THAN (1000) TABLESPACE ts_idx1,
  PARTITION p2 VALUES LESS THAN (5000) TABLESPACE ts_idx2,
  PARTITION p3 VALUES LESS THAN (MAXVALUE) TABLESPACE ts_idx3
);

2）哈希分區的全局索引

CREATE INDEX idx_sales_hash ON sales(sale_id)
GLOBAL PARTITION BY HASH (sale_id) PARTITIONS 4;

3）列表分區的全局索引

CREATE INDEX idx_sales_list ON sales(region)
GLOBAL PARTITION BY LIST (region)
(
  PARTITION p_east VALUES ('EAST') TABLESPACE ts_idx1,
  PARTITION p_west VALUES ('WEST') TABLESPACE ts_idx2,
  PARTITION p_other VALUES (DEFAULT) TABLESPACE ts_idx3
);

2、本地索引：按分區獨立維護，建議優先使用。

語法：

CREATE INDEX index_name 
ON table_name (column_list)
LOCAL
[PARTITION partition_name [TABLESPACE tablespace_name] 
  [PARAMETERS ('physical_attributes')], ...]
[PARALLEL n];

示例

– 簡單本地分區索引

CREATE INDEX idx_sales_local ON sales(sale_date) LOCAL;

– 帶表空間的本地分區索引

CREATE INDEX idx_sales_local ON sales(sale_date) LOCAL
(
  PARTITION p1 TABLESPACE ts_idx1,
  PARTITION p2 TABLESPACE ts_idx2,
  PARTITION p3 TABLESPACE ts_idx3
);

– 在分區表上創建本地分區索引

CREATE TABLE sales (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER
) PARTITION BY RANGE (sale_date) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD')),
    PARTITION p2 VALUES LESS THAN (TO_DATE('2023-04-01','YYYY-MM-DD')),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
CREATE INDEX idx_sales_date ON sales(sale_date) LOCAL;

本地分區索引意味着每個表分區都有一個對應的索引分區，維護成本低，當表分區維護時索引會自動維護，但是不能跨分區。

業務人員應根據實際的業務需求、維護需求等情況進行選擇和創建。此外，
可以使用 ALTER INDEX … REBUILD PARTITION … 可以重建單個索引分區，也可以使用 USER\_PART\_INDEXES 和 USER\_IND\_PARTITIONS 視圖查詢分區索引信息。

4.2.分區表使用時的注意事項

1、分區剪裁：儘量確保 WHERE 條件包含分區鍵，避免全分區掃描。
2、業務兼容性：確保應用程序的SQL包含分區鍵條件。
3、鎖競爭：避免在高峯期執行分區維護操作。

墨天輪從樂知樂享的數據庫技術社區蓄勢出發，全面升級，提供多類型數據庫管理服務。墨天輪數據庫管理服務旨在為用户構建信賴可託付的數據庫環境，併為數據庫廠商提供中立的生態支持。

服務官網：https://www.modb.pro/service

oracle , 性能優化 , SQL , 數據庫
user avatar dolphindb Avatar lanyiyun666 Avatar
Favorites 2 users favorite the story!
Favorites

Add a new Comments

Some HTML is okay.