PolarDB：ALTER TABLE…ADD PARTITION

工作原理

ALTER TABLE ... ADD PARTITION是一項中繼資料操作，但其效能和並發影響主要取決於表上索引的配置。

• 鎖行為 在執行ADD PARTITION期間，資料庫會擷取目標表上的ACCESS EXCLUSIVE鎖。此鎖會阻塞對該表的所有並發讀寫操作（SELECT, INSERT, UPDATE, DELETE）。鎖的期間主要由在新分區上建立索引所需的時間決定。

• 索引維護

  • 本地索引（LOCAL）：命令會自動在新的分區上建立對應的索引分割區。如果表包含多個本地索引，系統會為每個索引建立新的分區，這是操作耗時的主要原因。

  • 全域索引（GLOBAL）：全域索引的結構保持不變，無需額外維護。新分區中的資料在插入後會自動被全域索引覆蓋。

• 操作耗時

  • 無索引表：操作幾乎瞬時完成，因為它只涉及修改表的中繼資料定義。

  • 有索引表：操作耗時與在新分區上建立本地索引所需的時間成正比。

使用限制

• 基本定義規則

  1. 分區類型一致性：新添加的分區需與表的現有分區類型（LIST或RANGE）保持一致。

  2. 分區鍵一致性：新分區的分區規則需引用與表定義時完全相同的分區鍵列。

  3. 分區名稱唯一性：新分區的名稱在表的所有分區和子分區中需唯一。

• 分區值限制

  • MAXVALUE和DEFAULT分區限制

    • 限制：不能向已包含MAXVALUE分區的RANGE分區表或已包含DEFAULT分區的LIST分區表添加新分區。這是因為MAXVALUE和DEFAULT 分區已經覆蓋了所有未明確指定的值，邏輯上不允許再添加新的分區。

    • 解決方案：使用ALTER TABLE ... SPLIT PARTITION命令分割MAXVALUE或DEFAULT分區，從而騰出空間建立新分區。

      說明

      此操作可能會移動資料，產生較大的I/O和鎖開銷，強烈建議在業務低峰期或維護視窗執行。

      -- 樣本：分割 MAXVALUE 分區以添加 2024 年的分區
      ALTER TABLE sales SPLIT PARTITION max_partition AT (TO_DATE('2025-01-01', 'YYYY-MM-DD')) INTO (PARTITION p_2024, PARTITION max_partition);

  • RANGE分區順序要求 添加的新RANGE分區的VALUES LESS THAN值需高於表中所有現有分區的上限值。否則，操作將失敗並報錯ERROR:empty range bound specified for partition "xxx"。

  • LIST分區值唯一性 添加的新LIST分區的值不能與任何現有分區中的值重複。否則，操作將失敗並報錯ERROR:partition "xx" would overlap partition "xxx"。

• 許可權限制

  執行ALTER TABLE ... ADD PARTITION命令需要具備表的所有者許可權或為高許可權帳號。

操作建議

• 維護視窗執行：由於ADD PARTITION會擷取表級獨佔鎖定，執行時會阻塞對錶的DML和DDL操作。對於有本地索引的大型表，索引建立過程可能耗時較長，導致長時間的業務阻塞。建議在業務低峰期或指定的維護視窗內執行，以避免阻塞線上業務。

• 監控鎖等待：在執行操作期間，監控資料庫的鎖等待情況。如果鎖等待時間過長，可能需要終止操作並重新規劃。

• 分區數量建議：雖然資料庫在物理上對定義的分區數量沒有上限，但從管理和效能角度出發，建議將單個表的分區總數控制在1000個以內。過多的分區會增加查詢最佳化工具的解析成本，可能導致查詢效能下降。

文法參考

ALTER TABLE table_name ADD PARTITION partition_spec;

添加分區到LIST分區表

此操作用於為基於離散值（如國家、狀態代碼）列表分區的表添加新的資料類別。

1. 準備一個LIST分區表示例。以下sales表按country列進行分區。

   CREATE TABLE sales_list (
       dept_no     NUMBER,
       part_no     VARCHAR2(50),
       country     VARCHAR2(20),
       sale_date   DATE,
       amount      NUMBER
   )
   PARTITION BY LIST(country) (
       PARTITION europe VALUES ('FRANCE', 'ITALY'),
       PARTITION asia VALUES ('INDIA', 'PAKISTAN'),
       PARTITION americas VALUES ('US', 'CANADA')
   );

2. 使用ALTER TABLE ... ADD PARTITION命令添加一個名為east_asia的新分區，用於存放'CHINA'和'KOREA'的資料。

   ALTER TABLE sales_list ADD PARTITION east_asia VALUES ('CHINA', 'KOREA');

3. （可選）驗證新分區是否已成功添加。

   SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_list';

   查詢結果應包含新添加的east_asia分區。

添加分區到RANGE分區表

此操作用於為基於連續值（如日期、ID）定界分割的表添加新的時間段或數值範圍。

1. 準備一個RANGE分區表示例。以下sales表按sale_date列進行分區。

   CREATE TABLE sales_range (
       dept_no     NUMBER,
       part_no     VARCHAR2(50),
       country     VARCHAR2(20),
       sale_date   DATE,
       amount      NUMBER
   )
   PARTITION BY RANGE(sale_date) (
       PARTITION q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
       PARTITION q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
       PARTITION q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
       PARTITION q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
   );

   說明

   樣本中的日期格式使用了TO_DATE函數以確保格式的明確性。實際使用時，字串格式需符合資料庫的NLS_DATE_FORMAT設定。

2. 使用 ALTER TABLE ... ADD PARTITION 命令添加一個名為q1_2024的新分區。新分區的範圍需高於所有現有分區的上限。

   ALTER TABLE sales_range ADD PARTITION q1_2024 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));

3. （可選）驗證新分區是否已成功添加。

   SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_range';

   查詢結果應包含新添加的q1_2024分區，且其分區位置在最後。

添加分區到複合分區表

對於複合分區表（如RANGE-LIST），ADD PARTITION允許在添加主要磁碟分割的同時，定義其包含的子分區。

1. 準備一個RANGE-LIST複合分區表示例。

   CREATE TABLE composite_sales (
       sale_id     NUMBER,
       sale_date   DATE,
       region      VARCHAR2(20)
   )
   PARTITION BY RANGE(sale_date)
   SUBPARTITION BY LIST(region) (
       PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) (
           SUBPARTITION p_2023_north VALUES ('NORTH'),
           SUBPARTITION p_2023_south VALUES ('SOUTH')
       )
   );

2. 添加一個名為p_2024的新主要磁碟分割，並同時為其定義north和south兩個子分區。

   ALTER TABLE composite_sales ADD PARTITION p_2024
       VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) (
           SUBPARTITION p_2024_north VALUES ('NORTH'),
           SUBPARTITION p_2024_south VALUES ('SOUTH')
       );

3. （可選）驗證新的子分區是否已成功建立。

   SELECT partition_name, subpartition_name, high_value FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'composite_sales' AND partition_name = 'p_2024';

常見問題