🍒
データ爆増時代の必須技術！Oracleパーティション表でパフォーマンス改善にトライ

2025/01/09に公開
SQL
Oracle
Oracle 小ネタ
tech
 ごあいさつこんにちは、「エイジ＠フジワーランド」です

フリーでパッケージシステムのOEM供給やってます、最近暇なので時給でSESやってます

SESではOracleでデータウェアハウス作る手伝いさせてもらってますが、いろいろ勉強になってありがたいです

さて今回はパーティション表の話です

 SQL変更なしでパフォーマンス改善が見込めるパーティション表パーティション表の存在を知っていても、実際には使われていないケースが意外と多いようです

SQLの最適化やインデックスの見直しを行う前に、まずはパーティション表の適用を検討してみませんか？
もし、大量のデータを扱うテーブルがまだパーティション表になっていないなら、それは大きな改善のチャンスです！この記事を参考に、パーティション表の活用をぜひ始めてみてください

特に運用保守を担当されている方にとっては、これをきっかけに新たなビジネスの可能性が広がるかもしれません！最後までぜひご覧ください！

 パーティション表とはパーティション表は、データをテーブル内の特定の列（パーティションキー）に基づいて物理的に分割して管理するテーブルのことです。これにより、データのアクセス性能が向上し管理の負荷が期待できます
主な特徴：
データを小さな単位に分割して格納する
各パーティションは独立して管理可能
大量データに対する操作の効率化
パーティション表を利用することで、以下のようなメリットが得られます

クエリ性能の向上: 必要なパーティションのみを対象にするため、検索速度が向上

管理コストの削減: パーティション単位での操作が可能（例：古いデータの削除）

並列処理の有効化: 複数パーティションを並列に処理可能

 パーティション表使用はエディションに注意掲載SQLはXEで実行確認していますが、StandardEditionでは使えないかもです

SQLがエラーになる場合はEdtionの確認もお願いいたします

 この記事でわかること本記事では、以下の内容を簡潔にまとめています
パーティション表の基礎

パーティション表の仕組みや種類（レンジ、リスト、インターバルなど）を解説します
既存テーブルのパーティション化

既存テーブルのパーティション適用方法を解説します
パーティションの管理とバックアップ

パーティションごとのデータ確認、メンテナンス、バックアップ方法を具体例とともに紹介します

 1. パーティションの種類
 基本的なパーティションOracle Database では、主に以下のパーティションがサポートされています
レンジパーティション

データを範囲（例: 日付）に基づいて分割します
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('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
リストパーティション

特定の値に基づいてデータを分割します。例として、地域ごとにデータを分割する場合の構文です
CREATE TABLE customers (
    customer_id NUMBER,
    region VARCHAR2(50)
)
PARTITION BY LIST (region) (
    PARTITION p_r01 VALUES ('関東'),
    PARTITION p_r02 VALUES ('関西'),
    PARTITION p_other VALUES (DEFAULT)
);
DEFAULTは一致しない値すべてがこのパーティションに保存されます
ハッシュパーティション

データをハッシュ関数に基づいて分割します。例えば、データの均等分散を図りたい場合に有効です

顧客番号や商品コードなど種類が多く分類の基準が特にない場合はこれを用います
CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER
)
PARTITION BY HASH (customer_id)
PARTITIONS 4;

 さらに実用的なパーティションインターバルパーティション

レンジパーティションの拡張版で、範囲外のデータが挿入されると自動的に新しいパーティションが作成されます
 CREATE TABLE sales (
   sale_id NUMBER,
   sale_date DATE,
   amount NUMBER
 )
 PARTITION BY RANGE (sale_date)
 INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
   PARTITION p_start VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
 )
自動リストパーティション

リストパーティションの拡張版で、値ごとに自動で新しいパーティションが作成されます
CREATE TABLE transactions (
    transaction_id NUMBER,
    transaction_date DATE,
    region VARCHAR2(50)
)
PARTITION BY LIST (region) AUTOMATIC 
 (PARTITION p1 VALUES ('関東'));
);
コンポジットパーティション

複数の基準でデータを分割します。以下の例は「レンジ×リスト」の複合パーティションです
CREATE TABLE transactions (
    transaction_id NUMBER,
    transaction_date DATE,
    region VARCHAR2(50)
)
PARTITION BY RANGE (transaction_date)
SUBPARTITION BY LIST (region) (
    PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')) (
        SUBPARTITION p1r1 VALUES ('関東'),
        SUBPARTITION p1r2 VALUES ('関西')
    ),
    PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) (
        SUBPARTITION p2r1 VALUES ('関東'),
        SUBPARTITION p2r2 VALUES ('関西')
    )
);
※いちばん実装したいインターバルパーティションに自動でサブパーティション作成は調査中です

※サブパーティションテンプレートSUBPARTITION TEMPLATE あたりを使ってもよさそうですがこれも調査中…

 2. 既存テーブルへのパーティション適用既存の非パーティションテーブルに対してパーティションを適用することが可能です。この機能を利用することで、既存のデータベースを再設計せずにパフォーマンスを改善できます

※Oracle 12c以降の機能です
月単位にパーティション化する例
ALTER TABLE sales
MODIFY PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) -- 月単位でパーティションを自動生成
ONLINE
年単位にパーティション化する例
ALTER TABLE sales
MODIFY PARTITION BY RANGE (sale_date)
INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) -- 年単位でパーティションを自動生成
ONLINE

 3. パーティションの確認パーティションごとの件数を確認するには、DBA_TAB_PARTITIONSまたはUSER_TAB_PARTITIONSビューを使用します
以下は、パーティションごとの件数を確認するSQLです
SELECT 
    TABLE_NAME,
    PARTITION_NAME,
    NUM_ROWS
FROM 
    USER_TAB_PARTITIONS
WHERE 
    TABLE_NAME = 'SALES';
サブパーティションごとの件数の確認も可能です
SELECT 
    TABLE_NAME,
    PARTITION_NAME,
    SUBPARTITION_NAME,
    NUM_ROWS
FROM 
    USER_TAB_SUBPARTITIONS
WHERE 
    TABLE_NAME = 'SALES';
USER_TAB_PARTITIONSの NUM_ROWS は統計情報を元に表示されるため、最新の件数を取得するにはANALYZE TABLEまたはDBMS_STATSパッケージを使用して統計情報を収集する必要があります
以下はDBMS_STATSパッケージを使用して統計情報を収集する例です
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
      OWNNAME => 'YOUR_SCHEMA_NAME', 
      TABNAME => 'SALES');
END;
/
パーティション内のデータを確認するには SELECT文のテーブルにPARTITION句を追加してパーティションを指定します
SELECT * 
FROM sales PARTITION (p_2023);

 4. パーティションのメンテナンス運用中のパーティション表では、以下でメンテナンスが可能です
新しいパーティションの追加
ALTER TABLE sales 
ADD PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'));
新しいパーティションの追加（サブパーティションの場合）
   ALTER TABLE sales
   MODIFY PARTITION p_start
   ADD SUBPARTITION p_start_south VALUES ('South');
※もしデータがすでに存在する場合、VALUES ('South')に該当するデータが自動的に新しいサブパーティションに移動するわけではないため、必要に応じてデータ移動を手動で行う必要があります

 5. パーティションのバックアップ
 パーティション単位でデータをエクスポートする（Data Pump Export/Import を利用）Oracle Data Pumpを使用して、特定のパーティションのデータをエクスポートする方法です。この方法は、パーティションデータのバックアップや別の環境への移行に便利です
特定パーティションをエクスポート以下は、SALESテーブルのP_2023パーティションをエクスポートする例です
expdp userid=your_user/your_password DIRECTORY=dpump_dir DUMPFILE=sales_p2023.dmp LOGFILE=sales_p2023.log TABLES=SALES:P_2023
ちなみにインポート例は以下です
impdp userid=your_user/your_password DIRECTORY=dpump_dir DUMPFILE=sales_p2023.dmp LOGFILE=sales_p2023_import.log TABLES=SALES:P_2023

 パーティションを分割して別テーブルにエクスポートパーティション全体を別の一時テーブルにコピーし、そのテーブルをバックアップする方法です
CREATE TABLE sales_2023 AS
SELECT * 
FROM sales PARTITION (p_2023);

 6. パーティションの活用事例
 クエリ性能の向上パーティション表の最大の利点は、「パーティションプルーニング」と呼ばれる最適化機能です。クエリ実行時に必要なパーティションだけを対象とするため、検索速度が向上します
SELECT *
FROM sales
WHERE sale_date BETWEEN sysdate and (sysdate - 7);
上記のクエリでは、対象期間のパーティションのみがスキャンされるためパフォーマンス向上が期待できます

 データのライフサイクル管理古いデータを簡単に削除できます、例えば操作ログなど一定期間で不要になるものはDELETEを使用せずに削除ができるのでOracleに負荷なく処理ができます
ALTER TABLE sales
DROP PARTITION p1;

 並列処理の活用パーティション表では、パーティションごとに並列処理を行うことで、クエリの実行速度が期待できます

以下は、ヒント句に並列処理を指定してクエリを実行する例です
SELECT /*+ PARALLEL(sales, 4) */ 
       sale_date, SUM(amount)
FROM sales
WHERE sale_date BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
GROUP BY sale_date;

 7. 注意点とベストプラクティス
設計時にアクセスパターンを考慮
パーティショニングキーを適切に選択


インデックスとの組み合わせ
グローバルインデックスとローカルインデックスの使い分け


パーティション数の管理
過剰な分割は管理コストを増加させる可能性がある


 おわりにパーティション表…使ってみたくなりましたか？
よければこの記事を参考にしてパーティション表を積極的に活用してみてください

特に長期運用しているシステムでパーティション表を適用していない場合はアプリ側の修正無しでパフォーマンス改善が見込めるので是非検討してみてください

みなさんの作業効率が少しでもあがることを願います
最後まで読んできただきありがとうございました
Discussion

ログインするとコメントできます