Oracle PARTITIONの効果を検証する

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

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

Oracle PARTITIONの効果を検証する 1

図1:検証イメージ 

パーティションって?

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

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

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

Oracle PARTITIONの効果を検証する 2

検証内容

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

検証詳細

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

いまさら聞けない Oracleの基本 [初級編]

検証環境

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

マシン OS Windows7
CPU Intel Core i7-3770 3.40GHz
メモリ 8GB
その他 特に無し
DB 対象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件ごととしています。
(第1回参照)

いまさら聞けない Oracleの基本 [中級編]
新規CTA

続いて、パーティション化しない比較用のテーブルの作成です。同じようにデータ生成で用意してもよいのですが、ランダム生成ですので格納されるデータが違ってきます。データ長などがパフォーマンスに影響する可能性も考慮し、まったく同じデータを格納したものを作成したいと思います。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')

パーティション 1回目 2回目 3回目 4回目 5回目
あり 5.374 0.125 0.140 0.110 0.141
なし 12.012 10.343 10.717 10.203 10.093

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

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

追加検証

パーティションが一定の効果を示すことはわかりました。ではもう少し突っ込んで追加検証してみたいと思います。同じテーブルから、今度は四半期分のデータ(4月~6月)をカウントしてみたいと思います。ひとつは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')

パーティション 1回目 2回目 3回目 4回目 5回目
12分割 12.113 0.297 0.250 0.265 0.296
4分割 9.063 6.162 7.129 6.240 6.177

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

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

パーティション 1回目 2回目 3回目 4回目 5回目
4分割 6.926 3.292 3.526 3.167 3.151

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

結論

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

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

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

いまさら聞けない Oracleの基本

RELATED POST関連記事


RECENT POST「DBlab」の最新記事


DBlab

Oracle デッドロックの原因を追いかけてみる

DBlab

Oracle パーティション検証完結編

DBlab

Oracle B-treeインデックスとビットマップインデックス

DBlab

Oracle INDEXを作成したときのパフォーマンスへの効果を探る

Oracle PARTITIONの効果を検証する
新規CTA
ブログサイドバー_トライアル申込
ブログ購読のお申込み

RANKING人気資料ランキング

RANKING人気記事ランキング

RECENT POST 最新記事