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

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

Oracleのインデックスに関して調べているとインデックスにも幾つか種類があるのが分かります。B-treeインデックス、ビットマップインデックス、ファンクションインデックス、逆キーインデックス・・・。今回はその中でもB-treeインデックスとビットマップインデックスの違いにフォーカスを当て検証したいと思います。

一般的にこの２つは下記のような特徴が説明されています。
B-treeインデックスは規定のインデックスでカーディナリティ(選択性)が高い列に有効なインデックスである。一方、ビットマップインデックスはカーディナリティが低い列に有効なインデックスである。

※カーディナリティが低い列・・・値の種類が少ない列のこと例えば10万レコードあるが値の種類は「Y」「N」のみ　といった状態。そしてビットマップインデックスの使用は [値の種類数]÷[レコード数]が1％未満の時に検討します。

今回はこの説明がパフォーマンスにどの程度影響があるか検証していきたいと思います。

検証方法

（検証１）
カーディナリティが高いデータを用意し、B-treeインデックス・ビットマップインデックス・インデックス無し　の３パターンを比較します。

（検証２）
カーディナリティが低いデータを用意し、検証１と同様に３つのパターンを比較します。

検証環境

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

クライアント検証機	OS	Windows7 pro
	ビット数	64bit
	CPU	Intel Core i7-4790
	メモリ	16GB
	その他	DBも同検証機上に配置

Oracleデータベース	バージョン	Oracle12ｃR1（12.1.0.1）
	エディション	Enterprise Edition
	ビット数	64bit
	メモリサイズ	1GB

検証１ - カーディナリティが高い場合

（検証１ – 準備）
下記のような単純な構造のテーブルを作成。

--テーブル作成

CREATE TABLE "TBL_TEST001A"

(

"F_ID" NUMBER NOT NULL,

"F_NAME" VARCHAR2(30),

CONSTRAINT "PK_TBL_TEST001A" PRIMARY KEY ("F_ID") USING INDEX

)

このTBL_TEST001Aテーブルにデータを100万件セットします。このときF_NAME列は乱数で生成し、カーディナリティの高い状態にします。

データ生成が完了した後、コピーで同じデータが格納されたTBL_TEST001Bテーブル、TBL_TEST001Cテーブルを作成します。それからTBL_TEST001AのF_NAME列にB-treeインデックス、TBL_TEST001BのF_NAME列にビットマップインデックスを付与します。
それでは準備が整いましたので検証していきましょう。

（検証１ – 実験）

B-treeインデックスを使用した場合

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

ビットマップインデックスを使用した場合

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

インデックス無しの場合

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

（検証１ – 結果）
想定どおりB-treeインデックスのCOSTが小さく、次点でビットマップインデックス、最後にインデックス無しという結果になりました。ビットマップインデックスのCOSTがインデックス無しの状態に近い値でした。

検証２ - カーディナリティが低い場合

（検証２ – 準備）
テーブル構造は検証１と同様で名前がTBL_TEST002Aを作成します。このテーブルにデータを100万件セットします。先ほどはF_NAME列に乱数をセットしましたが、今回はカーディナリティの低い状態にするため、
‘A’ ‘B’ ‘C’ のいずれかが入るようにデータ生成をします。

データ生成が完了した後、コピーで同じデータが格納されたTBL_TEST002Bテーブル、TBL_TEST002Cテーブルを作成します。それからTBL_TEST002AのF_NAME列にB-treeインデックス、TBL_TEST002BのF_NAME列にビットマップインデックスを付与します。
それでは準備が整いましたので検証していきましょう。

（検証２ – 実験）

B-treeインデックスを使用した場合

ビットマップインデックスを使用した場合

インデックス無しの場合

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

（検証２ – 結果）
想定どおりビットマップインデックスのCOSTが一番小さかったです。しかしB-treeインデックスは使用されず、フルスキャンになりました。

（検証２ – 追加実験）
気になったので下記のようにヒント句で強引にインデックスを使用した場合を実験しました。

SELECT /*+ INDEX(TBL_TEST002A IDX_TBL_TEST002A) */

* FROM TBL_TEST002A

WHERE F_NAME = 'A'

ヒント句でインデックスを強制使用した場合

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

（検証２ – 追加実験の結果）
フルスキャンよりB-treeインデックスを使用したほうがCOSTは大きくなるという結果になりました。 [RELATED_POSTS]

総括

リファレンスどおり、B-treeインデックスはカーディナリティが高い列に有効なインデックスであり、ビットマップインデックスはカーディナリティ(選択性)が低い列に有効なインデックスであることが分かりました。また、カーディナリティが低い列にB-treeインデックスを使用した場合、インデックスが使われずにフルスキャンになる点や、ヒント句で無理やり使用した場合はフルスキャンよりパフォーマンスが落ちてしまう結果が出た点は意外でした。

ビットマップインデックスは仕様上の制限がありますのであまり利用されませんが、B-treeインデックスで思うようなパフォーマンスが出ない。もしくはフルスキャンになってしまうという場合はビットマップインデックスを試してみると良いかもしれません。