Oracle PARTITIONの効果を検証する

2015.07.01 株式会社システムインテグレータ

Oracle9iよりパーティション機能が実装されています。テーブルデータを指定した法則に基づいて分割管理することにより、検索時のパフォーマンスの向上が見込まれます。大量データを操作する場合に効果を発揮するといわれていますが、果たしてどの程度のものなのでしょうか。今回はこのパーティションについて検証していきたいと思います。

Oracle PARTITIONの効果を検証する 1

図１：検証イメージ

パーティションって？

検証の前に、パーティションについて少し触れておきます。テーブルデータをパーティションに区切って管理するのは前述のとおりですが、それにはいくつかの種類があり、用途によって使い分けることが重要となります。

種類	概要	使用例
レンジ・パーティション	パーティションキーの値の範囲で分割。日付ごとに区切ることが多い。	年間売り上げデータを月ごとにパーティション化する。
リスト・パーティション	関連性のない不連続なデータを指定。任意に区切る場合に使用。	年間売り上げデータを各地方ごとに集計する場合など。関東ならパーティションに東京・神奈川・埼玉・千葉・群馬・茨城・栃木を指定
ハッシュ・パーティション	上記のように明示的にパーティション化できない場合に指定。
コンポジット・パーティション	上記を組み合わせた指定方法。	より細やかなパーティション化を実行したいとき。

通常はデータを検索する際、何もしていない状況では全データから抽出することになりますが、パーティション化しておくことにより、必要な部分のみ参照することが可能になり、その分データの抽出速度が上がる、といった仕組みのようです。
注意点としては、パーティション化したからといってすべてのSQLが早くなるわけではないということです。
パーティション機能が実行されるには、SELECTする際にパーティション化する際に指定したキー(カラム)をWhere句に指定する必要があります。(図は月ごとに12分割した例)

Oracle PARTITIONの効果を検証する 2

検証内容

今回はいくつかあるパーティションの中から、最も一般的であるといわれているレンジ・パーティションを採用します。顧客情報を模したテーブルに大量データを投入し、月ごとにパーティション化したテーブルと、通常状態のテーブルでデータ抽出し、そのレスポンスを比較します。

検証詳細

・日付型を持ったサンプルデータに1000万件のデータを作成
・日付は2015/01/01～2015/12/31の一年間のデータをランダムに設定
・公正を保つため、データごとテーブルをコピーする
・片方は日付型カラムをキーにしたレンジ・パーティションを構成し、一ヵ月ごとにパーティション化する
・SQLを実行し、一か月分の件数を集計。そのレスポンスを測定。
その際インデックススキャンとならないようにカウントに*ではなく明示的に項目を指定
・SQL実行前にキャッシュをクリアし、その後各テーブルで５回処理を行う。
※５回実行する間、キャッシュクリアは行わない。
※上記を３回繰り返した中間値を採用する。

検証環境

今回の検証環境は以下の通りです。

マシン

Windows7

CPU

Intel Core i7-3770 3.40GHz

メモリ

8GB

その他

特に無し

対象RDBMS

Oracle11.0.2.0.1.0

テーブル名

TEST_PART

テーブル項目

CODE	NUMBER(10)　※主キー
NAME	VARCHAR2(40)
ADD1	VARCHAR2(50)
ADD2	VARCHAR2(100)
TEL	VARCHAR2(20)
UPDDATE	DATE

※TEST_PARTは「UPDDATE」をキーに月ごとにパーティション化
同内容・同データで「TEST_PART2」を複製し、こちらは通常のテーブルとする。

検証準備

まずはテーブルを作成し、データを1000万件投入します。こちらは SI Object Browser(以下OB) のデータ生成機能を使用します。もうおなじみですね。以下のように設定しました。

カラム名	データ生成方法
CODE	連番。1から開始し1ずつカウントアップ
NAME	テンプレート　苗字 + 名前
ADD1	テンプレート　都道府県
ADD2	テンプレート　住所
TEL	乱数値　電話番号
UPDDATE	乱数値　日時（時間はセットしない） 2015/01/01から2015/12/31の間でランダムに生成

また、データ生成の効率を考え、コミットタイミングは1000件ごととしています。
(第１回参照)

続いて、パーティション化しない比較用のテーブルの作成です。同じようにデータ生成で用意してもよいのですが、ランダム生成ですので格納されるデータが違ってきます。データ長などがパフォーマンスに影響する可能性も考慮し、まったく同じデータを格納したものを作成したいと思います。SQLを一つ一つ書いて実行することもできますが、ここはOBのオブジェクトコピー機能を使用しましょう。
方法は簡単です。オブジェクトリストから該当のオグジェクトを選択（ここではTEST_PART）し、右クリックメニュー「コピー」その後同メニューの「貼り付け」を行うだけです。CTL＋C→CTL＋V　の、所謂コピペ動作でも実行できます。

Oracle PARTITIONの効果を検証する 3

後はテーブル名(と制約名)を変更すれば終了です。ここでは「TEST_PART2」とします。名前はあれですが、こちらはパーティション化しないで使用します。

Oracle PARTITIONの効果を検証する 4

テーブルのCREATEと、データのINSERTを自動で行ってくれます。

最後に、「TEST_PART」テーブルをパーティション化します。こちらもOBで設定可能です。
テーブルオブジェクトを開き、領域情報タブを表示します。

Oracle PARTITIONの効果を検証する 5

今回は下記内容でデータを1ヶ月ごとにパーティション化しました。パーティション名と上限値だけ設定し、後はOracle側の自動設定にゆだねました。

パーティション名	上限値
PA01	TO_DATE('2015-01-31','YYYY-MM-DD')
PA02	TO_DATE('2015-02-28','YYYY-MM-DD')
PA03	TO_DATE('2015-03-31','YYYY-MM-DD')
PA04	TO_DATE('2015-04-30','YYYY-MM-DD')
PA05	TO_DATE('2015-05-31','YYYY-MM-DD')
PA06	TO_DATE('2015-06-30','YYYY-MM-DD')
PA07	TO_DATE('2015-07-31','YYYY-MM-DD')
PA08	TO_DATE('2015-08-31','YYYY-MM-DD')
PA09	TO_DATE('2015-09-30','YYYY-MM-DD')
PA10	TO_DATE('2015-10-31','YYYY-MM-DD')
PA11	TO_DATE('2015-11-30','YYYY-MM-DD')
PA12	TO_DATE('2015-12-31','YYYY-MM-DD')

検証結果

テーブルから5月分のデータ件数を取得するSQLを実行しました。
結果は以下のとおりです。
※実行したSQL

SELECT COUNT(UPDDATE)
FROM TEST_PART (TEST_PART2)
WHERE UPDDATE
BETWEEN TO_DATE('2015-05-01','YYYY-MM-DD')
AND TO_DATE('2015-05-31','YYYY-MM-DD')

パーティション	１回目	２回目	３回目	４回目	５回目
あり	5.374	0.125	0.140	0.110	0.141
なし	12.012	10.343	10.717	10.203	10.093

※単位：秒　対象件数は849,771

ばっちり効果が出たようですね。初回実行時も約２倍以上のレスポンスでしたが、特筆すべきは二回目以降、キャッシュができた後の処理速度です。パーティション「なし」のほうは微減だったのに対して、「あり」のほうはほぼ一瞬で結果が取得できています。

追加検証

パーティションが一定の効果を示すことはわかりました。ではもう少し突っ込んで追加検証してみたいと思います。同じテーブルから、今度は四半期分のデータ(４月～６月)をカウントしてみたいと思います。ひとつは12分割されたTEST_PARTを使用、もうひとつは4分割されたTEST_PART2を作成し、同様の検証を行います。

※実行したSQL

SELECT COUNT(UPDDATE)
FROM TEST_PART (TEST_PART2)
WHERE UPDDATE
BETWEEN TO_DATE('2015-04-01','YYYY-MM-DD')
AND TO_DATE('2015-06-30','YYYY-MM-DD')

パーティション	１回目	２回目	３回目	４回目	５回目
12分割	12.113	0.297	0.250	0.265	0.296
４分割	9.063	6.162	7.129	6.240	6.177

※単位：秒　対象件数は2,500,462

上記のような結果となりました。初回こそ４分割が上回ったものの、その後は圧倒的に12分割に軍配が上がりました。ちなみに、4分割したテーブルから1か月分を抽出した結果は以下です。

パーティション	１回目	２回目	３回目	４回目	５回目
４分割	6.926	3.292	3.526	3.167	3.151

※単位：秒　対象件数は849,771

結論

今回の検証結果では、以下のようになりました。
・パーティション化はレスポンス向上に効果がある
・キャッシュ発生後の効果は絶大
・細かくパーティション化したほうが、全体的なレスポンスは向上する

やはり、パーティションには一定の効果があったようです。さらに、キャッシュ発生後はパーティションがない状態と比較して飛躍的にレスポンスが向上することがわかりました。
最後の「細かくパーティション化したほうが、全体的なレスポンスは向上する」については、今回のように単純な集計の場合がたまたま当てはまっただけかもしれないので、更なる検証の必要があるかもしれませんが、単純な参照で、検索範囲が未確定の場合などは、細かくパーティション化しておいたほうが、後々便利かもしれません。

今回の検証は以上となります。パーティション設定の際、少しでも参考にしていただければ幸いです。