PolarDB:ALTER TABLE...ADD PARTITION

仕組み

ALTER TABLE ... ADD PARTITION はメタデータ操作ですが、そのパフォーマンスと同時実行性への影響は、主にテーブルのインデックス構成によって決まります。

• ロックの動作: ADD PARTITION を実行すると、データベースはターゲットテーブルに対して 排他ロック を取得します。このロックは、テーブルに対するすべての同時読み取りおよび書き込み操作 (SELECT、INSERT、UPDATE、および DELETE) をブロックします。ロックの所要時間は、主に新しいパーティションにインデックスを作成するために必要な時間によって決まります。

• インデックスのメンテナンス

  • ローカルインデックス: コマンドは、新しいパーティションに対応するインデックスパーティションを自動的に作成します。テーブルに複数のローカルインデックスがある場合、システムはそれぞれに対して新しいパーティションを作成します。これが、操作の所要時間の主な理由です。

  • グローバルインデックス: 構造は変更されず、新しいパーティションに挿入されたデータを自動的にカバーするため、追加のメンテナンスは必要ありません。

• 操作の所要時間

  • インデックスのないテーブル: この操作はテーブルのメタデータを更新するだけなので、ほぼ瞬時に完了します。

  • インデックスのあるテーブル: 操作の所要時間は、新しい空のパーティションにローカルインデックスを作成するために必要な時間に比例します。

制限事項

• 基本的な定義ルール

  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 の値は、既存の最も高いパーティションの上限よりも大きい必要があります。そうでない場合、操作は失敗します。

  • LIST パーティション値の一意性

    新しい LIST パーティションの値は、既存のどのパーティションの値とも重複できません。そうでない場合、操作は ERROR: partition "xx" would overlap partition "xxx" のようなエラーで失敗します。

• 権限要件

  ALTER TABLE ... ADD PARTITION を実行するには、テーブルのオーナーであるか、特権アカウントを使用する必要があります。

ベストプラクティス

• メンテナンスウィンドウでの実行: ADD PARTITION はテーブルに排他ロックを取得するため、DML および DDL 操作をブロックします。ローカルインデックスを持つ大規模なテーブルの場合、インデックス作成プロセスが長くなる可能性があり、長時間のサービス中断を引き起こすことがあります。オンラインサービスをブロックしないように、この操作はオフピーク時間帯またはスケジュールされたメンテナンスウィンドウ中に実行することをお勧めします。

• ロック待ちの監視: 操作中は、データベースのロック待ちを監視します。ロック待ち時間が長すぎる場合は、操作を終了して再スケジュールする必要がある場合があります。

• 推奨されるパーティション数: テーブルが持つことができるパーティション数に物理的な制限はありませんが、管理上およびパフォーマンス上の理由から、単一テーブルの合計パーティション数を 1,000 未満に保つことをお勧めします。パーティション数が多すぎると、クエリオプティマイザーの解析コストが増加し、クエリのパフォーマンスが低下する可能性があります。

構文

ALTER TABLE table_name ADD PARTITION partition_spec;

LIST パーティションテーブルへのパーティションの追加

この操作を使用して、国や状態コードなどの離散値のリストでパーティション化されたテーブルに、新しいデータカテゴリを追加します。

1. サンプルの LIST パーティションテーブルを作成します。次の sales_list テーブルは、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_range テーブルは、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 の 2 つのサブパーティションを定義します。

   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';

よくある質問