【虎】第15回「SQLパーティション表を再評価する」

SQLパーティション表を再評価する

Db2 for iのSQLパーティション表は古くからある機能ですが、その存在をご存知の方は多くないようです。
SQLパーティション表は、膨大なデータをテーブルに格納し、格納先から条件に基づく効率的なデータの抽出と処理を可能にします。膨大な量のデータを分析するアプリケーションなどが普及し、SQLパーティション表が提供する機能が多くのユーザーから求められる時代になって、ようやく活躍の場が得られるようになりました。
しかも、SQLパーティション表は、IBM i 7.5からは無償で利用できる点も見逃せません。対談記事の抄訳からIBM iのSQLパーティション表の活用方法をご確認ください。(編集部)

2024年5月1日　チャーリー・グアリーノ

IBM i のアプリケーション・アーキテクトであるスヴェン・ヤンソン氏が、SQLパーティション表とデータ整理の新たな可能性について語ります。

SQLパーティション表とは?

チャーリー・グアリーノ（以下、チャーリーと略します）：
多くの人はSQLパーティション表とは何なのかをよく理解していません。そもそもSQLパーティション表とは何で、どのように機能し、その利点は何かを教えてください。

スヴェン・ヤンセン(以下、スヴェンと略します)：
かつて、物理ファイルの場合は1.7TB、SQL表の場合は約40億行が制限でした。それぞれではなく、どちらかが先に到達した時点で制限となるため、IBMはSQLパーティション表をリリースして、さらに多くのデータを1つの表に格納可能としました。

IBM iのSQLパーティション表は、リリース7.1か7.2で導入されました^{（訳注1）}。リリース7.5のパーティション表は無料ですが、それ以前は有償でした。

SQLパーティション表を使用すると、１つの表に最大256個のパーティションを含められ、各パーティションに1.7TBのデータが格納できます。すなわち、1つの表に最大300～400TBのデータが格納できます^{（訳注2）}。

チャーリー：
1つの表で300TBものデータを必要とするアプリケーションが存在するのでしょうか?

スヴェン：
300TBのデータではなかったかもしれませんが、大手銀行でクレジットカード・アプリケーションを担当していた時に、SQLパーティション表を使いました。

クレジットカード取引は、カード番号、金額、場所のみではなく、他の多くのデータも含んでいるため、すぐに1.7TBを使い果たしてしまいます。

当初、18か月分のデータを見たいという要件に対して、約3か月分のデータを持つ物理ファイルと、18か月分のデータを持つ会計ファイルを用意して、これら2つのファイルを結合させていました。ただ、データを移動するたびに多くの手作業が必要でしたので、SQLパーティション表に移行しました。

SQLパーティション表が最も優位性を発揮するのは、時間の経過とともにデータが使われなくなる場合です。記憶が正しければ、クレジットカード業界の場合、データの変更が許されるのは半年間です。半年を経過して変更不可となっても、クレジットカードの場合は継続的に情報が利用できる必要があるため、SQLパーティション表に最適です。

担当していたクレジットカード・アプリケーションの場合、1か月あたりのデータ量を調べたところ、500GBでした。そこで、各月をパーティションとし、列でパーティションを定義しました。列はこの定義範囲内の番号で始まり、定義範囲内の別の番号で終わります。例えば、最初のパーティションはID番号1から始まり100まで増えるというような定義をしました。

SQLパーティション表作成の考慮点

チャーリー：
SQLパーティション表は、事前の検討抜きでは作成できないと聞いています。パーティションを作成する際の考慮点を教えてください。

スヴェン：
自分で設計する必要があります。しかし、一度パーティションを作り、それぞれのパーティションに範囲値を設定すれば、後は自動的に機能するようになっています。移行元となる物理ファイルを用意し、替わりとなる表を作成して、パーティションを追加後、移行元の物理ファイルからデータを表にコピーしたところ、私が担当していたアプリケーションではうまくいきました。

また、SQLパーティション表は、パーティション列でもあるキー列を使って読み込むため、必要なデータが存在するパーティションを把握できています。パーティションが200個あって、それぞれのパーティションに100GBのデータがあったとしても、データが存在しているパーティションだけを探しに行くため、パフォーマンスの観点で問題となることはありません。

チャーリー：
SQLパーティション表の利点とは、例えば日付でパーティションが区切られていても、週全体のデータが必要な場合は、SELECT文を実行するとパーティションをまたいで自動的に全てのデータを読み取れる点だと思いますが、いかがでしょう？

スヴェン：
確かにそうです。かつて、物理ファイルとメンバーがあった頃は、「このメンバーのデータを読み取りたい」と伝えるために、データベース・ファイルをオーバーライドする必要がありました。

現在は、SQLにエイリアス（別名）がありますが、SQLパーティション表ではエイリアスを気にする必要はありません。SELECT文を実行すると、選択条件に指定する列がキー列でない場合、すべてのパーティションが検索されるのはもちろんのこと、その表のキー列であってもすべてのパーティションが検索されます。

ただし、インデックスについては、少し戦略的に考える必要があります。テーブル・スペース・スキャンなどが実行されないようにするために、インデックスは用意する必要があります。

チャーリー：
Db2が非常に効率的なので、インデックス・アドバイザーが役立つように思います。ただ、特定の行や範囲などを継続的に検索または選択している場合、Db2は一時インデックスを作成し続ける替わりに、パフォーマンス向上のためにインデックスを推奨することがあります。

スヴェン：
SQLを実行するのが年に1回程度なのであれば、インデックスは不要かもしれません。エイリアスを使用して、特定のパーティションから読み取る指定も可能です。とはいえ、日常的に実行する場合はデータがどのパーティションにあるかを考える必要はありません。クエリを実行するだけです。

チャーリー：
パーティションの名前を選択したり、ジャーナルのように保持して値を追加して値を増やし続けるグローバル形式を選択できますか?

スヴェン：
現在使用している表やプログラム等に対する独自の命名規則を設定できます。Db2 for i 用の自動的な命名機能はありませんので、テーブルの場合と同じ基準でパーティションに名前を付ける必要があります。

SQLパーティション表の結合

チャーリー：
例えば、明細100件ごとに区切っていたパーティションのうち、200件目から500件目をひとまとめにしたい場合、パーティションを結合する機能はありますか？それとも、パーティションの区切りは一度設定したらもう変更はできないのでしょうか？

スヴェン：
結合はできますが、手作業が必要です。例えば、範囲値が200～299、300～399、400～499 の3つのパーティションがあり、これらを1つのパーティションにまとめたいとします。この場合、これらを別の表に移動して新しいパーティションを作成し、データをコピーして戻します。

また、範囲値101～200のパーティションがある場合、範囲値が300の行は挿入できません。ただし、どのパーティションがどの範囲値を持つかを知る必要はありません。また、パーティションと範囲が存在する限り、データがどのパーティションに配置されるかを知らなくても構いません。挿入または更新、あるいはデータを削除したい場合、皆さんは行いたい操作をするだけです。必要な処理はシステムが自動的に行います。

SQLパーティション表の活用シナリオと考慮点

チャーリー：
スヴェンさん、説明を伺っていると、SQLパーティション表の使用は、必ずしも大量のデータがある場合に限定する必要がないように思えてきました。異なるメンバーを持つ利便性を享受するという観点だけでも、SQLパーティション表を小さな表に使って良いように思います。

スヴェン：
私達のSQLパーティション表の始め方は、まさに、小さな表からでした。私達は表がそれ程大きくならないことは分っていましたが、どのように機能するかを見てみることにしました。同僚とも話していますが、SQLパーティション表は完璧に機能しています。

チャーリー：
つまり、ある程度の量のデータは必要だけれど、SQLパーティション表を選択肢として検討するために膨大な量のデータは必要ないということですね。

スヴェン：
膨大な量のデータは不要です。SQLパーティション表に興味があり、詳細を知りたい場合は、まずは実稼働環境で始めることをお勧めします。小さな表から始めて、それがどのように機能するかを確認してください。その後で、どのように作業したいかをよく考えてください。

チャーリー：
SQLパーティション表は、IBM i 7.5では無料で利用できるオプションだとおっしゃいましたね。

スヴェン：
記憶が正しければ、オプション27です。Db2 Multisystemという製品をIBMに注文する必要がありますが、7.5以降は無料です。

チャーリー：
それは素晴らしい。これをもっと使用しない理由はありません。

スヴェン：
その通りです。そして良い点は、何らかの理由でファイルまたは表をF仕様書で宣言している場合でも、必要に応じてインデックスを論理ファイルとして使用でき、その場合も完全に機能することです。

ただし、これら旧来のネイティブI/Oのやり方でSETLL、READE、CHAINなどを使っている場合、唯一の考慮事項は、インデックス作成時にインデックス・オプションとしてNOT PARTITIONEDを指定する必要があるということです。そうしないと、ファイルをオープンしたときにそのインデックスの最初のパーティションが取得されます。

ですから、そのインデックス内のすべてのパーティションを取得したい場合、NOT PARTITIONEDを指定したインデックスを論理ファイルとして使用します。

SQLだけを実行している場合は違いがありませんが、ネイティブI/Oを使用している場合は違いがありますから、心に留めておいてください。

チャーリー：
運用中のSQLパーティション表の役に立つかもしれない、ネイティブの新しいSQLサービスはありますか?

スヴェン：
QSYS2にはSYSCOLUMNSやSYSINDEXESのようないくつかのビューがあり、表内でのパーティションの分割され方やその範囲のような、表内のパーティションに関する情報が保持されています。

チャーリー：
なるほど。データがどのように管理され、ディスクに保存されているかを再考するきっかけになると思います。

スヴェン：
表に100TBのデータがある場合のバックアップ方法として、ほとんどの組織が使用しているBRMSに、変更されたパーティションをバックアップするように指示できます。ただし、パーティション自体またはその中のデータが変更されていない場合、BRMSはバックアップを行いません。

実際には、オンラインに各種のPDFがアップロードされており、最適化について全て説明されています。

また、パーティションを変更したり、表に新しいパーティションを追加したりすると、BRMSはパーティションの変更日を検索します。そして、その表内のすべてのパーティションの変更日を更新してしまうため、結果としてバックアップに時間がかかってしまいます。これは、新しいパーティションを追加するときに考慮するべきことです。

そういえば、SQLパーティション表を異なる方法で使用しているIBM i のユーザーがいると聞きました。どのパーティションがあるかが分かれば、入ってくるデータに応じて異なるパーティションを読むことができ、パフォーマンスが向上します。お客様の中には、水平パーティショニング^{（訳注3）}の替わりに別の方法でそれを行った人がおり、パフォーマンスが驚く程向上したと言っていました。複数の小さな表ではなく、1つの大量のデータをもつ表があることは、確かに全体像の把握に役立つでしょう。

^{（訳注1）}パーティション表は古くから存在していましたが、7.1及び7.2で制限事項が緩和され実用性が向上しました。具体的には7.1で参照整合性制約をもたせることや、識別列を持つ表をパーティション表にできるようになり、7.2でUPDATE命令によるパーティションキーの更新が可能になりました（それ以前は該当列をDELETEした後INSERTする必要がありました）。

^{（訳注2）}詳しく説明すると、パーティション表の限界値は約435TBまたは1兆行のどちらか先に到達した方です。

^{（訳注3）}水平パーティショニングとは、各パーティションの列構成は同じで、パーティションキー列の値（例えば年や月）に基づいて、データが格納されるパーティションが決まるような構造のパーティション定義をすることです。

本記事は、TechChannelの許可を得て「IBM i: The Tables Have Turned」（2024年5月1日公開）を翻訳し、日本の読者にとって分かりやすくするために一部を更新しています。最新の技術コンテンツを英語でご覧になりたい方は、techchannel.com をご覧ください。