Oracle RANGE PARTITIONの効果を検証する

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

2015年7月に公開した、「パーティションの効果を検証する」では、題名のとおりパーティション化したデーブルに対する速度検証を行ないました。(参照)結果はご覧のとおりだったのですが、ユーザー様より「同じデータで、「月」カラムを追加で用意し、それにインデックスを張った場合は、一ヵ月ごとにパーティション化した場合と比較して、どっちが速いのか」といった追加実験のご要望をいただきました。

今回は、このテーマで実施したいと思います。

無題1-300x213.png

検証内容

前回同様、顧客情報を模したテーブルに大量データを投入し、月ごとにパーティション化したテーブルと、インデックスを付加したテーブルでデータ抽出し、そのレスポンスを比較します。

パーティションテーブルには、レンジ・パーティションを採用します。双方に月単位をあらわす新しいカラムを追加し、片方にはインデックスを付加します。

検証詳細

・日付型を持ったサンプルデータに1000万件のデータを作成

・日付は2015/01/01~2015/12/31の一年間のデータをランダムに設定

・公正を保つため、データごとテーブルをコピーする

・片方は日付型カラムをキーにしたレンジ・パーティションを構成し、一ヵ月ごとに

パーティション化する

・片方は月単位をあらわすカラムにインデックスを付加する。

(カラムとその内容は両方のテーブルに追加)

・SQLを実行し、一か月分の件数を集計。そのレスポンスを測定。

・SQL実行前にキャッシュをクリアし、その後各テーブルで5回処理を行う。

※5回実行する間、キャッシュクリアは行わない。

※上記を3回繰り返した中間値を採用する。

検証環境

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

マシン情報 OS Windows7
CPU Intel Core i7-3770 3.40GHz
メモリ 8GB
その他 特に無し
DB 対象RDBMS Oracle11.0.2.0.1.0
サンプルテーブル名 TEST_PART , TEST_INDEX
項目 CODE     NUMBER(10) ※主キー
NAME      VARCHAR2(40)
ADD1     VARCHAR2(50)
ADD2     VARCHAR2(100)
TEL       VARCHAR2(20)
UPDDATE   DATE
UPDMONTH CHAR(2) ←追加

※TEST_PARTは「UPDDATE」をキーに月ごとにパーティション化

同内容・同データで「TEST_PART2」を複製し、こちらは通常のテーブルとする。

※TEST_INDEX は「UPDDATE」にインデックスを付加する。データは「UPDDATE」の月の

データベース開発に関するお役立ち資料

部分が入る。('01' '02'など) 

検証準備

前回同様SI Object Browser(以下OB) のデータ生成機能を使用し、データを1000万件投入

します。

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

今回追加した「UPDMONTH」ですが、残念ながらこちらは仕様にあわせた生成はできませんので、別途算出して設定します。

コピー機能を使用してテーブルを複製し「TEST_INDEX」と名づけ、「UPDMONTH」にインデックスを付加します。

「TEST_PART」テーブルには、前回と同様のパーティション設定を行ないました。(詳細をご覧になりたい方はこちら)

それでは検証開始です。

検証結果

テーブルから5月分のデータ件数を取得するSQLを実行しましす。

結果は以下のとおりです。

 

※TEST_PART(パーティション設定したテーブル)

SELECT COUNT(UPDMONTH)

FROM   TEST_PART

WHERE UPDDATE

BETWEEN TO_DATE('2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

AND     TO_DATE('2015-05-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS')

 

※TEST_INDEX(インデックス設定したテーブル)

SELECT COUNT(UPDMONTH)

FROM   TEST_INDEX

WHERE UPDMONTH = ‘05’

 

パーティション 1回目 2回目 3回目 4回目 5回目
パーティション 3.197 1.748 1.763 1.747 1.731
インデックス 2.309 0.031 0.047 0.062 0.047
通常 14.195 11.809 11.653 12.058 11.778

※単位:秒 対象件数は852,535

インデックスの勝利となりました。前回検証時とDB環境が変化しているため、パーティションのほうはまったくおなじ結果とはなっておりませんが、やはり通常時よりも速く、キャッシュ発生時はさらに速くなっています。それでも、インデックスを付加したケースにはかないませんでした。 

追加検証

とはいえ、今回のお題はインデックスの効果が出やすいようなテーブル設定であることも事実です。そこで、なるべく公平になるようにTEST_PARTからパーティション設定を解除し、UPDDATEに新たにインデックスを付加したテーブルを用意します。

SQLもおなじものを実行し、速度を計測します。

 

※実行したSQL

SELECT COUNT(UPDMONTH)

FROM   TEST_PART

WHERE UPDDATE

BETWEEN TO_DATE('2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')

AND     TO_DATE('2015-05-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS')

 

  1回目 2回目 3回目 4回目 5回目
  1.3953 0.062 0.031 0.047 0.062

※単位:秒 対象件数は852,535

今回実験で最速の結果となりました。 [RELATED_POSTS]

結論

今回の検証では、パーティションの効果はあるものの、インデックスを付加した速度を超えることはありませんでした。

しかし、パーティションが最速になるケースが必ずあるはずです。

分割の仕方や、データ量が異なれば、COUNTじゃなければ、そもそもほかに適したパーティション化があるのでは・・・・

などなど、まだまだ掘り下げられそうです。

今回はここまでとしますが、次の機会に追加検証を行なって行きたいと思います。

新規CTA

RECENT POST「DBlab」の最新記事


この記事が気に入ったらいいねしよう!
OBトライアル
ブログ購読のお申込み

RANKING人気資料ランキング

RANKING人気記事ランキング

RECENT POST 最新記事