VLDBとパーティショニングTweet新連載が始まります!たろーちゃんに続き、プラチナホルダーの新連載が始まります。今回のネタは「VLDBとパーティショニング」です。ともお目通しください!助けて!ボスにドヤされちまう!キャサリン「どうしたの?チャーリー。浮かない顔をしてるわね。」チャーリー「聞いてくれよ、キャサリン。今回新しくデザインしたこの履歴テーブルなんだけど、ホラ、レコード作成日付っていうカラムがあるだろ?これを条件にして四半期ごとに不要レコードを削除するという運用になったんだが、それが1億件以上あるんだよね・・・。試しに検証してみたら、何時間待ってもDELETEが終わらないんだ!こんなバッチ処理リリースしたらまたボスにドヤされちまう!」キャサリン「パーティション・テーブルは検討してみた?」チャーリー「パーティ・・・何だって?そんなもの知らないよ。僕はさっさとこの忌々しいDELETE野郎の尻を叩いてスティーブの店でご機嫌なピザと一杯やりたいだけなんだよ。」キャサリン「だから、その履歴テーブル、レコード作成日付でパーティション化すれば過去履歴レコードに対してTRUNCATEやDROPも使えるし、あっという間に終わるわよ。他の業務処理のクエリの高速化も期待できるわね。今回紹介する『パーティション化』とは、テーブルの特定のカラムを分割キー(『パーティション化キー』)として、その値の種類によりテーブルを物理的に複数のセグメントに分割することで、クエリやメンテナンス作業のパフォーマンスを向上させる技術です。利点は様々ありますが、特に大きい以下の2点を挙げておきます。SQLによりアクセスする物理領域を一部に限定することで、読み込むブロックを減らし、処理を高速化できる(パーティション・プルーニング)パーティションごとに物理セグメントが分割されており、個々のパーティション単位でDROPやTRUNCATEなどの管理作業が行える。SCANとなった場合でも動作するため、SQL要件によっては非常に強力です。1処理でのアクセス範囲を最小限に絞ることができるということは、同様の処理を複数並列に実行しやすく、パラレル処理との相性が抜群ということも言えます。大事なのは、この機能はアプリケーションから完全に透過的に働くということです。実体は分割されていても論理的には1つのテーブルに見えていますので、SQLを書く側は対象のテーブルがパーティション化されていることを意識することなく、同じSQLでも上記条件さえ満たしていればパーティション・プルーニングは有効に作用するということです(もちろん、使う側が知っているに越したことはないのですが)。2点目については、一つのテーブルだった場合はテーブル全体が対象となってしまうDROPやTRUNCATEをパーティション単位で行うことができ、また他のパーティション作業に影響なく(ロックをかけず)実施できる(並列で実施できる)点が非常に便利となります。これらの利点はシステムのライフサイクルが進行し、抱えるデータが増大すればするほど威力を発揮します。Editionなどでは使えません。パーティション・テーブルの主な種類ここではよく使用される主な3種類のパーティションと、その作成方法、選び方をご紹介します。レンジ・パーティションキー項目の値の範囲でパーティション分割します。挿入されるレコードの、『このキー項目の値がココまでの値だったらこのパーティション、ココまでならあっちのパーティション』といった具合です。”4月未満”、”7月未満”、”10月未満”と指定された3つパーティションがあった場合、キー項目が”4月”のレコードは(4月以上)7月未満なので2番目のパーティションに挿入されます。このレコードの振り分けはもちろん、Oracleが自動でやってくれます。レンジ・パーティションテーブルを作成するCREATE文のサンプルは以下のようになります。ts5);赤字の箇所が定型文になります。パーティション名になります。ここでは主キーの作成は省略していますが、もちろん主キーを設定することもできます。主キーを含め、パーティション・テーブルに設定する索引については次回以降に説明します。“maxvalue”は予約語で、直前のパーティション(ここではq4)よりも大きいキー値全てのレコードを担当する”受け皿”として使用することができます。上記例では、2020/1/1以降のレコードは全てここに挿入されます。maxvalueを持つパーティションの作成を省略した場合、2020/1/1のレコードをINSERTしようとするとエラーになります。サンプルから見てもわかる通り、各パーティションごとに配置する表領域を指定することができます。複数パーティションに並列してアクセスする場合、各パーティションを異なる表領域(データファイル)に配置し、物理的にアクセスする領域を分散する施策をとることもあります。ハッシュ・パーティションキー項目の値から、決められた数のパーティションに自動的に均等に割り振る分割方式です。レコードが挿入または検索される場合、対象レコードのキー値から、Oracle内部で『ハッシュ化関数』と呼ばれるロジックを通して機械的に対象のパーティションが判別されます。これにより、無限に近い、あるいは数があらかじめ不明な種類をもつキー値のレコードを一定数のパーティションにほぼ均一に均(なら)すことができるのがハッシュ・パーティションの特長です。『ハッシュ』ってそもそも何?一般的に、『ハッシュ化』の一番単純なロジックの代表は、『キー値を分割数で割った余り』で算出することです。任意の数字を4つのグループ(=パーティション数)に振り分けたい場合、その数(=キー値)を4で割った余りをグループ番号(パーティション番号)とします。1を4で割った余りは1に、5を4で割った余りも1になります。...といったように、キー値がまんべんなく存在する場合、その範囲がどんなに広くても、ある決められた数のグループにまんべんなく振り分けられます。Oracleのハッシュ・パーティション化で使用されているロジックはもう少し複雑(キー値には文字列も使えます)ですので上記そのままではありません(ロジックは公開されていません)。ハッシュ・パーティションテーブルを作成するCREATE文のサンプルは以下のようになります。ts4);レンジ・パーティションよりも構文がすっきりしていることから、ハッシュの場合は基本的に分割数以外には(振り分けロジックなどを)ユーザーは指定できない(する必要がない)ことがわかります。パーティション名や配置表領域の指定を簡略化(Oracleまかせに)した構文として、以下のようにも記述することができます。()内に記述する表領域は分割数と同じ数だけ用意する必要はありません。()内の表領域に自動的に分散して配置されます。このように、ハッシュ・パーティションは、SQLのアクセスを分散しパーティション・プルーニングなどにより性能を向上したいが、キー値の種類は不特定なため割り振りルールはOracleに任せたい、といった場合に最適です。リスト・パーティションキー項目の値が、事前に決まっている複数の値のどれに該当するか、で分割する方式です。レンジ・パーティションが連続したキー値で判断するのに対し、リスト・パーティションは完全に固定された、取りうる値をキー値として全てリストアップします。リスト・パーティションテーブルを作成するCREATE文のサンプルは以下のようになります。ts6);サンプルのように、指定するキー値は一つのパーティションに対して複数指定することができます。上記例ではパーティションr1へは、キー値が’Sapporo’または’Sendai’のどちらがきても挿入されます。“default”は予約語で、リストされたどのキー値にも当てはまらないレコードを担当する”受け皿”として使用することができます。defaultを持つパーティションの作成を省略した場合、リストにないキー値のレコードをINSERTしようとするとエラーになります。まとめ今回はパーティションの基本的な形3つを紹介しました。それぞれの特長をまとめますと以下のようになります。特長レンジキー値の範囲で分割する。各範囲は作成時に指定する。「日付」や連続するIDなどの数字で分割したい場合に有効。カーディナリティーの高いキー値を元に分散させたい場合に有効。リストキー値をあらかじめ決められた値で分割する。キー値の取りうる値の種類があらかじめ判明していてその数が少ない場合に有効。テーブルを分割する目的を明確に把握し、是非最適な分割方式を選択するようにしましょう。次回ではさらに特殊な形式のパーティションについて触れていきたいと思います。キャサリン「どう?パーティションの概要、大体わかった?」チャーリー「よくわかったよ、キャサリン。よし、僕自身がボスにバラバラにパーティション化される前にさっそく例の履歴テーブルを作成日付でレンジ・パーティション化する方向で再デザインしてみようと思うよ。インフラソリューション事業部に在籍するPlatinumホルダー。アプリケーション開発からOracleコンサルまで幅広く経験。当サイトの一部ビジュアルデザインも担当。infra-db@sts-inc.co.jp「DBひとりでできるもん」運営チームです。社員等の情報をお届けします。60名弱の事業部員で鋭意、執筆中です。