OracleDatabaseパーティションドロップについて

大規模なテーブルでは保存期間切れのデータの削除処理時間が無視できません。そんな時に有効なパーティション削除機能ですが、グローバル索引があるとメンテナンスに注意が必要です。そういうパーティションを使う上での基本的なお話しです。

Transcript

1. 2026年10月22日 【JPOUG#17】 パーティションのグローバル 索引とメンテナンスについて 株式会社 日本総合研究所 カード業務システム本部 部長 兼 シニアエキスパート 佐脇 秀登
2. 自己紹介 佐脇 秀登(SAWAKI Hideto) • 株式会社 日本総合研究所においてクレジットカードシステム(オープン系)開発を所管する本部の 部長（ミッションクリティカルで大規模なシステムの責任者） • また、社内でシニアエキスパート（ITアーキテクト）としての職位に認定され、全社の大規模案件の アーキテクチャ検討支援を行うとともに、技術相談やログ解析などトラブルシューティング支援にも従事 • 本部内の新人研修も企画、運営 保有資格 • ORACLE MASTER Platinum 8i • 高度情報処理技術者試験 x 8 • ネットワークスペシャリスト • プロダクションエンジニア • データベーススペシャリスト • セキュリティ（今の情報処理安全確保支援士相当） • アプリケーションエンジニア（今のシステムアーキテクト相当） • プロジェクトマネージャ • ITサービスマネージャ • ITストラテジスト Copyright (c) The Japan Research Institute, Limited
3. はじめに Oracle Databaseのパーティショニングオプションを使っていますか？ 年月日を項目に持つ大規模テーブルに対するSQLの高速化などに便利な機能です。 特に保存期限の切れた古い年月のデータを削除する際に、月単位でパーティション分割しておく と、パーティションドロップにより一瞬で削除が終わり、delete 文を大量発行する処理に比べて 非常に速く処理が終わります。 しかし、非同期グローバル索引メンテナンスという機能の仕様を知らないと、思わぬタイミングで データベースサーバに負荷がかかったり、アーカイブログが想定以上に出力されて戸惑うことがあり ます。 こういった場合の注意点をご紹介します。 Copyright (c) The Japan Research Institute, Limited
4. Oracle Partitioning とは •オブジェクト(表、索引)を複数に 分割する Oracle Database Enterprise Edition の 有償オプション機能 •分割されたオブジェクト = パーティション •パーティション分割に利用される列 = パーティション・キー 表 表 パーティション・キー 2022年パーティション （400万件） 過去データ のメンテナンス 2022年～2025年 （4,000万件） 直近データの 参照、更新、集計 過去データ のメンテナンス 2023年パーティション （800万件） 2024年パーティション （1,200万件） 2025年パーティション （1,600万件） 直近データの 参照、更新、集計 Copyright (c) The Japan Research Institute, Limited
5. ユースケース 以下のような効果が見込めます フルスキャンのアクセス範囲を限定 • アクセス競合の分散 データ量が増加してもアクセス範囲は表全体に影響されないため、膨大な 履歴データを持つ表などを特定期間で集計、分析する処理に適している。 • • 2025年6月 2025年5月 当月分の 集計/分析 ・・・ 2025年1月 ・・・ 2025年4月 2010年4月 1年分の 集計/分析 四半期分の 集計/分析 • 削除範囲を絞り込んでも大量のI/Oが発生し処理が遅延するケースでは、 パーティション単位で削除することで効率的＆高速に処理可能。 パーティション単位での論理バックアップの取得や、最新パーティションの みの索引再構築、統計情報取得など柔軟に運用ができる。 用途に応じた最適なデバイスに配置 • 参照頻度に応じて、高速または安価な低速のディスクに配置することで 様々なHWリソースを有効活用できる。 2025年 2025年6月 2025年5月 索引再構築 統計情報取得 (DataPump) Export ・・・ 2023年 2022年 読み取り 専用 移動 ＆ 圧縮 表 索引 大量データのメンテナンス時間を短縮 • B*Tree索引に対し、シーケンス値などを用いた単調増加する値を同時に INSERTする場合、 Right Growing Index（索引リーフ・ブロック最右端の 競合）が発生する。この競合問題を解消するために索引をパーティション 化する。 表も同様に同一ブロック内の異なる行に対して同時に更新が発生すると ブロック競合発生する。この競合問題を解消するのに表をパーティション 化する。 ・・・ 2024年 2023年 2022年 ・・・ 2010年 2010年 Drop or Truncate 高速 低速 Copyright (c) The Japan Research Institute, Limited
6. パーティションの主な種類 レンジ（インターバル※） リスト(自動リスト※) 時系列(期間)、番号等の値の範囲で分割 地域、製品カテゴリ等の「任意の値リスト」で分割 2025年4月 2025年5月 2025年6月 MAXVALUE ※自動的にパーティションを作成（MAXVALUEは指定不可） インターバルは等間隔のみ 関東 関西 ・・・ 九州 DEFAULT ※自動的にパーティションを作成（DEFAULTは指定不可） ハッシュ コンポジット 列値をハッシング・アルゴリズムに基づいて分割 パーティションをさらにサブパーティションに分割 SYS_P<n> SYS_P<n> SYS_P<n> SYS_P<n> Copyright (c) The Japan Research Institute, Limited
7. レンジ・パーティション • パーティション・キーを範囲で分割する。日付列で分割することが多い。 • 最初のパーティションに下限値はなく、以降のパーティションの範囲は1つ前のパーティションの上限値以上～ 自パーティションの上限値未満。 • NULLを指定することはできない • いずれのパーティションの範囲にマッピングされない値を格納する場合は MAXVALUE を定義する。 表 (T1) 2025年4月 （p_202504） 2025-05-01未満のデータが p_202504のパーティション表に 格納される 2025年5月 （p_202505） CREATE TABLE T1 ( prod_id number ,time_id date time_idがパーティション・キー 条件値は該当パーティションに ,place varchar2(20) 含まれない ) PARTITION BY RANGE (time_id) ( PARTITION p_202504 VALUES LESS THAN(TO_DATE(‘2025-05-01’,’yyyy-mm-dd’)) ,PARTITION p_202505 VALUES LESS THAN(TO_DATE(‘2025-06-01’,’yyyy-mm-dd’)) ,PARTITION p_202506 VALUES LESS THAN(TO_DATE(‘2025-07-01’,’yyyy-mm-dd’)) ,PARTITION p_maxvalue VALUES LESS THAN(MAXVALUE) ); いずれのパーティションにもマッピングしない値を 格納する場合は「MAXVALUE」を設定する 2025年6月 MAXVALUEについて MAXVALUE について （p_202506） •• 上限値を設けたくない場合に最上位のパーティションとして定義する 上限値を設けたくない場合に最上位のパーティションとして定義する MAXVALUE （p_maxvalue） 2025-07-01以降のデータが p_maxvalueのパーティション表 に格納される •• パーティション・キーがNOT パーティション・キーがNOTNULLでない場合は、NULLを格納できる NULLでない場合は、NULLを格納できる • NULLはどの値よりも大きいと評価されるため、レンジ・パーティションの最上位パーティションにMAXVALUEが指定されて • NULLはどの値よりも大きいと評価されるため、レンジ・パーティションの最上位パーティションにMAXVALUEが指 いない場合は、NULLを格納できず以下のエラーが発生する 定されていない場合は、NULLを格納できず以下のエラーが発生する ORA-144000：挿入されたパーティション・キーはどのパーティションにもマップされません。 ORA-144000：挿入されたパーティション・キーはどのパーティションにもマップされません。 Copyright (c) The Japan Research Institute, Limited
8. パーティション表とローカル索引の関係 ローカル（パーティション）索引は表のパーティション・キー列を指定時に特定のパーティション表にのみ アクセスします。 表のパーティション・キー列を含む索 引アクセス ローカル索引（同一キー）の作成コマンド例 パーティション・キー 索引キー C1 C2 CREATE INDEX t1_idx1 ON t1 (c1) C3 C4 partition by list (c1) 東 京 パーティション表1 埼 玉 SELECT * FROM t1 WHERE c1 = ‘東京’; 表のパーティション・キー ( partition p_kanto values ('茨城県','栃木県','群馬県','埼玉県','千葉県','東京都','神奈川県') (関東) 千 葉 索引キー LOCAL; ,partition p_chubu values ('新潟県','富山県','石川県','福井県','山梨県','長野県','岐阜県','静岡県','愛知 県') C1 C2 C3 C4 ,・・・(省略)・・・ ) パーティション表2 (中部) C1 C2 C3 表と同じパーティションの 定義が省略されているイメージ C4 パーティション表n Copyright (c) The Japan Research Institute, Limited
9. ローカル索引とグローバル索引 グローバル索引は表のパーティション・キー列を指定しないため、複数のパーティション表にアクセスする ことがあります。 表のパーティション・キー列を含む索 引アクセス パーティション・キー 索引キー C1 100 パーティション表1 101 (関東) 102 C2 C3 C4 100 大量アクセス時は特 定パーティション表の みをフルスキャン 表のパーティション・キー列を含まない 索引アクセス C4 千葉 C1 大量アクセス系 表のパーティション・キー列 は表フル・スキャン時に パーティション・プルーニン グを効かせる列 C3 東京 埼玉 SELECT * FROM t1 WHERE c1 = ‘東京’; C2 索引キー パーティション表2 101 (中部) SELECT * FROM t1 WHERE c4 = 100; 102 C1 C2 C3 C4 100 パーティション表n 101 102 トランザクション系 表のパーティション・ キーと関係ない索引設 計が可能 Copyright (c) The Japan Research Institute, Limited
10. パーティション表の削除とローカル索引 パーティション表を削除した場合、ローカル索引は自動削除されます。 ➢ パーティション表の削除コマンド ALTER TABLE <table_name> DROP PARTITION <パーティション表1>; パーティション・キー 索引キー C1 C2 索引キー C3 東京 100 パーティション表1 (関東) SELECT * FROM t1 WHERE c1 = ‘東京’; C4 千葉 101 埼玉 102 C1 C2 C3 C4 100 パーティション表2 (中部) C1 C2 C3 SELECT * FROM t1 WHERE c4 = 100; 101 102 C4 100 パーティション表n 101 102 Copyright (c) The Japan Research Institute, Limited
11. パーティション表の削除とグローバル索引 •パーティション表を削除した場合、グローバル索引は使用不可（UNUSABLE）になります。 • 削除対象のパーティションが空の場合は影響ありません。 •グローバル索引が使用可能な状態を維持するには、UPDATE INDEXES句を指定します。 ➢ パーティション表の削除コマンド ALTER TABLE <table_name> DROP PARTITION <パーティション表1> UPDATE INDEXES; パーティション・キー 索引キー C1 C2 索引キー C3 C4 東 10 京 パーティション表1 0 (関東) 千 葉 埼 玉 SELECT * FROM t1 WHERE c1 = ‘東京’; C1 C2 C3 10 1 10 2 C4 10 パーティション表2 0 (中部) 10 1 10 2 C1 C2 C3 パーティション表n SELECT * FROM t1 WHERE c4 = 100; C4 10 0 10 1 UPDATE INDEXES句を指定しないと UNUSABLEになる ↓ UPDATE INDEXES句を指定すると パーティション表の削除は瞬時に終わり グローバル索引も利用可能 10 2 めでたしめでたし Copyright (c) The Japan Research Institute, Limited
12. ところが 誰も見ていない午前2時、先送りされた負荷がシステムを襲う SYS.PMO_DEFERRED_GIDX_MAINT_JOB 夜間のメンテナンスウィンドウに Oracleの自動スケジューラが起動。 日中に遅延されていた孤立エントリの クリーンアップを突如開始 → 負荷が上がって、アーカイブログが大量発生 10:00 AM DBA実行: 0.0x秒で完了 02:00 AM Copyright (c) The Japan Research Institute, Limited
13. グローバル索引の非同期メンテナンス（補足事項） •12c以降のUPDATE INDEXES句を指定したDROP/TRUNCATEでは、孤立したエントリをクリーンアップするグローバル索引の メンテナンスは非同期で行われるため、DDLは高速に実行される • 索引に残された不要なエントリのクリーンアップ、領域の整理はDBMS_SCHEDULERの PMO_DEFERRED_GIDX_MAIN_JOBで行われる • デフォルトでは毎日2時のメンテナンスウィンドウに実行される（スケジュールは変更可能） • 手動でも実行できる ➢ パーティション表の削除を update indexes 句を指定して実行（デフォルトで2時にグローバル索引の非同期メンテナンスのジョブが実行される） alter table RANGE_T1 drop partition p_202204 update indexes; ➢ 非同期グローバル索引メンテナンスの自動スケジューラ・ジョブを手動実行 connect / as sysdba EXEC DBMS_SCHEDULER.RUN_JOB('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', TRUE); ➢ 内部的には、削除または切り捨てられたレコードに対する孤立した索引エントリを削除するコマンドが実行される ※10046トレースから確認 ALTER INDEX "PRTUSR"."RANGE_T1_G_IDX" MODIFY PARTITION "SYS_P48918" COALESCE CLEANUP PARALLEL 1; Copyright (c) The Japan Research Institute, Limited
14. 注意点 パーティションドロップの負荷テストはメンテナンスウィンドウ（非同期メンテナンス） も含めて計測しましょう •「0.0x秒で完了、やったぁ」だけでなく、グローバル索引の非同期メンテナンスの影響も確認 特にアーカイブログの出力量の計測に注意 •（当たり前ですが）負荷テスト時には可能な限り本番環境と同じ分布・サイズを準備 しましょう ・グローバル索引のキー項目の数 ・削除されるパーティションの行数 → メンテナンスされるブロック数が本番と全然違うと、処理時間やアーカイブログの出力量 を過少見積りしてしまいます 非同期メンテナンスは孤立エントリの削除だけなので、エクステントサイズが縮小しません。 INDEX FAST FULL SCAN（例えばグローバル索引のキー項目指定のcount） の処理速度の悪化が許容できないなら、定期的にリビルドによる再編成も検討しましょう。 Copyright (c) The Japan Research Institute, Limited
15. ご清聴ありがとうございました 本資料の内容は個人の見解であり、所属する組織の公式見解ではありません。 Copyright (c) The Japan Research Institute, Limited