概要
便利なパーティションですが、主キーへのインデックスはグローバル・インデックスとなりがちです。この場合、DROP PARTITIONによるパーティション・メンテナンスで索引メンテナンスも必要になることから、悩みどころになります。この記事ではその問題に対してここまで解決できるという内容を扱います。
課題：パーティション表の主キーへのインデックスはグローバル・インデックスとなる問題
・パーティションで享受したいメリットとして、DROP PARTITIONなどによる改廃メンテナンスの高速化は大きい
・このとき、ローカル・インデックスであれば、「パーティションごとにインデックスを持つ」ため索引はメンテナンス影響をうけない
・しかし、ローカル・インデックスでは、主キー列がパーティションキーを含んでいない場合、あるパーティションに存在する値と別パーティションに存在する値の重複を検出できない。
・一方、主キー列をパーティションキーにしたい要件があるかというと、パーティション化してDROP改廃やパーティション・プルーニングのメリットを享受したい列とは異なる、というジレンマがある
例：
上記でいうところの、主キーはパーティション・キーにしにくい、を以下表の例で説明します。
CREATE TABLE sales (
sales_id NUMBER,
sales_date DATE,
amount NUMBER
)
PARTITION BY RANGE (sales_date);
ALTER TABLE sales ADD CONSTRAINT sales_pk PRIMARY KEY (sales_id);
sales_dateのような日付列は、よく分析で日次、週次、月次レポートで使うし、改廃も月単位などでバッサリDROPできると便利なので、のレンジ・パーティションのキーに指定したい。一方、sales_idのような主キーはパーティション・キーに指定してもこういったメリットが考えにくいところです。
そうなると、sales_id がパーティションキーに含まれていないためグローバル索引ということになります。この場合、DROP PARTITIONのときに、裏でグローバル索引のメンテナンスも実施され、待つ必要がある、というジレンマがおきます
解決策1：非同期グローバル索引メンテナンスを使う
12cR1からの非同期グローバル索引メンテナンスを活用できます。
DROP PARTITIONやTRUNCATE PARTITIONを実施したときは索引メンテナンスなしで、グローバル索引は引き続き使用可能。グローバル索引のメンテナンスはあとから実施（デフォルトAM2:00開始）、という機能です。DROP/TRUNCATEしたところは、索引メンテナンスされるまでは、参照先がのデータがない(Orphaned)エントリとして索引には残りますが、SQL実行時は、ユーザーにはエラーではなくアクセスできないキーとしてフィルタリングされるようになります。filter(TBL$OR$IDX$PART$NUM( … )) という形で実行計画でも確認できます。
解決策2：主キーにパーティション・キーとしてふさわしい情報を含める
例えば、主キーの決め方でsales_dateとsales_idを組み合わせた番号を採用すると、主キーは以下のようになります。
20250101000001
20250101000002
・・・
20250131000100
20250201000101
・・・
このとき
CREATE TABLE sales(
)
PARTITION BY RANGE (sale_id) (
PARTITION p2023m01 VALUES LESS THAN (20250201000000),
・・・
とすれば、主キーを使って月次のレンジパーティションのパーティション・キーにもできます。
パーティションの性質を知った上で主キーのIDの振り方を決めなければいけない調整面での難しさがあります。
解決策3：運用やDB以外でカバー
こちらは、あまり触れませんが、以下のように運用やDB以外の部分でカバーすることも充分検討に値します。
・DROP PARTITIONのときにグローバル索引更新の時間も確保する
・パーティション表ではローカル索引のみとし主キーを諦める、主キー制約相当のことはアプリ側のキーの払い出し制御などでカバー