2015年7月に公開した、「パーティションの効果を検証する」では、題名のとおりパーティション化したデーブルに対する速度検証を行ないました。(参照)結果はご覧のとおりだったのですが、ユーザー様より「同じデータで、「月」カラムを追加で用意し、それにインデックスを張った場合は、一ヵ月ごとにパーティション化した場合と比較して、どっちが速いのか」といった追加実験のご要望をいただきました。
今回は、このテーマで実施したいと思います。
![いまさら聞けない Oracleの基本 [初級編]](https://no-cache.hubspot.com/cta/default/2975556/050bae9a-78e5-464b-9f1e-9fdb573b01ef.png)
検証内容
前回同様、顧客情報を模したテーブルに大量データを投入し、月ごとにパーティション化したテーブルと、インデックスを付加したテーブルでデータ抽出し、そのレスポンスを比較します。
パーティションテーブルには、レンジ・パーティションを採用します。双方に月単位をあらわす新しいカラムを追加し、片方にはインデックスを付加します。
検証詳細
・日付型を持ったサンプルデータに1000万件のデータを作成
・日付は2015/01/01～2015/12/31の一年間のデータをランダムに設定
・公正を保つため、データごとテーブルをコピーする
・片方は日付型カラムをキーにしたレンジ・パーティションを構成し、一ヵ月ごとに
パーティション化する
・片方は月単位をあらわすカラムにインデックスを付加する。
(カラムとその内容は両方のテーブルに追加)
・SQLを実行し、一か月分の件数を集計。そのレスポンスを測定。
・SQL実行前にキャッシュをクリアし、その後各テーブルで５回処理を行う。
※５回実行する間、キャッシュクリアは行わない。
※上記を３回繰り返した中間値を採用する。
検証環境
今回の検証環境は以下の通りです。
| マシン情報 | 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'など)
![いまさら聞けない Oracleの基本 [中級編]](https://no-cache.hubspot.com/cta/default/2975556/be092016-22ca-411d-bb85-eb38c49174f3.png)
検証準備
前回同様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’
| パーティション | １回目 | ２回目 | ３回目 | ４回目 | ５回目 |
| パーティション | 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.3953 | 0.062 | 0.031 | 0.047 | 0.062 |
※単位：秒 対象件数は852,535
今回実験で最速の結果となりました。 [RELATED_POSTS]
結論
今回の検証では、パーティションの効果はあるものの、インデックスを付加した速度を超えることはありませんでした。
しかし、パーティションが最速になるケースが必ずあるはずです。
分割の仕方や、データ量が異なれば、COUNTじゃなければ、そもそもほかに適したパーティション化があるのでは・・・・
などなど、まだまだ掘り下げられそうです。
今回はここまでとしますが、次の機会に追加検証を行なって行きたいと思います。
![いまさら聞けない Oracleの基本 [初級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b.png)
![いまさら聞けない Oracleの基本 [中級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b2.png)
