ロックに関する検証 その5

~ロックに関する検証 その5~
ペンネーム ちゃむ

前回は、インデックスが作成してあるテーブルに対して更新処理を行ったとき
の、トランザクションロックに関しての検証を行った。
今回からは、ビットマップ・インデックスを作成したテーブルに対して、更新
処理を行なってみる。その時のロックの仕組みを検証する前に、ビットマップ・
インデックスの構造を十分に理解する必要がある。

<B-Treeインデックスとビットマップ・インデックスの構造上の比較>

<B-Treeインデックス>

B-Treeのインデックスの構造は、前回説明した通りである。
B-Treeインデックスが使用されて検索が行われると、該当するキー値が存在す
るリーフブロックが読み込まれ、それが指しているROWIDにより実データにアク
セスする。カーディナリティが高い(値の種類が多い)場合は、非常に有効で、
例えばプライマリキー列によるイコール検索などは抜群に速いことは体感した
ことがあると思う。しかし、カーディナリティが低い場合は、同じ値が複数存
在するので、データをあまり絞り込むことができず、実データのアクセス数が
増えてしまう。このような場合は、全件検索よりも遅い場合もある。

<ビットマップ・インデックス>
ビットマップ・インデックスの構造のイメージ図を以下に示す。

実は、ビットマップ・インデックスも、内部的にはB-Treeインデックスのリー

フブロックの中に、上記のURLで示されたイメージ図のような形式で納められて
いる。
したがって、ビットマップ・インデックスもB-Treeインデックスと同様に、
TREEDUMPを取得することができる。

ビットマップ・インデックスは、通常、構造的にB-Treeインデックスよりもデー
タの収納効率が良い。それは、1行ずつにカラム値とROWIDを持っているB-Tree
インデックスと比べて、上記のイメージ図のように、START ROWIDとEND ROWID
の範囲の中で、カラム値に該当するかどうかを「0」と「1」のビットで表現し
ているからだ。

また、ビットマップ・インデックスは、構造上、カラム値のカーディナリティ
が低いほど収納効率が良い。一方のB-Treeインデックスは、カーディナリティ
の違いによって収納効率が変わってくるものではない(あくまでもカラム長の
みで決まる)。

では、この収納効率に関して検証してみよう。

以下の検証では、EMP表を10万件に拡張したT10MAN_COPYというテーブルを元に
する。

SQL> DESC t10man_copy

 名前                                           NULL?    型
 ---------------------------------------------- -------- -------------
 EMPNO                                          NOT NULL NUMBER
 ENAME                                                   VARCHAR2(20)
 JOB                                                     VARCHAR2(18)
 MGR                                                     NUMBER(4)
 HIREDATE                                                DATE
 SAL                                                     NUMBER(7,2)
 COMM                                                    NUMBER(7,2)
 DEPTNO                                                  NUMBER(2)

EMPNOは、1から100000までの連番である。

これを利用し、カーディナリティ4のMGR列を作ってみよう。
そのために、MOD関数を用いる。MOD(m,n)はmをnで割った余りを返す関数である。
また、カラム長をそろえるため、to_charを使った。

********** MGR列のデータをカーディナリティ4にするためのSQL ***********

SQL> CREATE TABLE T10MAN_COPY_4 AS
     SELECT EMPNO,ENAME,JOB,to_char(mod(EMPNO,4),'0000000') MGR,
            HIREDATE,SAL,COMM,DEPTNO
     FROM T10MAN_ORG ;

表が作成されました。

以下 0000000、0000001、0000002、0000003 が交互に検索される様子

SQL> CREATE INDEX BTREE_4 ON T10MAN_COPY_4(MGR)
     STORAGE (INITIAL 2K NEXT 2K PCTINCREASE 0 MAXEXTENTS UNLIMITED) ;

エクステントの大きさを確認する

SQL> SELECT SEGMENT_NAME,BLOCKS,EXTENTS FROM DBA_SEGMENTS
     WHERE SEGMENT_NAME = 'BTREE_4' ;

SEGMENT_NAME            BLOCKS   EXTENTS
-------------------- --------- ---------
BTREE_4                   1242      1233

********************* カーディナリティ100の場合 **********************

SQL> CREATE INDEX BTREE_100 ON T10MAN_COPY_100(MGR)
     STORAGE (INITIAL 2K NEXT 2K PCTINCREASE 0 MAXEXTENTS UNLIMITED) ;

SQL> SELECT SEGMENT_NAME,BLOCKS,EXTENTS FROM DBA_SEGMENTS
     WHERE SEGMENT_NAME = 'BTREE_100' ;

SEGMENT_NAME            BLOCKS   EXTENTS
-------------------- --------- ---------
BTREE_100                 1240      1235

******************** カーディナリティ100000の場合 ********************

SQL> CREATE INDEX BTREE_100000 ON T10MAN_COPY_100000(MGR)
     STORAGE (INITIAL 2K NEXT 2K PCTINCREASE 0 MAXEXTENTS UNLIMITED) ;

SQL> SELECT SEGMENT_NAME,BLOCKS,EXTENTS FROM DBA_SEGMENTS
     WHERE SEGMENT_NAME = 'BTREE_100000' ;

SEGMENT_NAME            BLOCKS   EXTENTS
-------------------- --------- ---------
BTREE_100000              1238      1237

カラム長が同じなので、各インデックスの大きさには殆ど差がなかった。これ
は、値とROWIDをペアで持つというB*Treeインデックスの構造を理解していれば、
ある程度は予想できた結果であろう。
それぞれのEXTENTS数の若干の違いは、ブランチブロック数の違いである。また、
INITIAL=NEXT=2K、PCTINCREASE=0で作成しているので、通常は、カーディナリ
ティ100000の場合のように、EXTENTSの値 = BLOCKSの値 + 1ブロック(セグメ
ントヘッダー分)になるはずであるが、他の場合だと当てはまらない。
これは、フラグメントを抑えるための、「5ブロック以下のフリーブロックを残
さない動き」に関係する。詳しくは、バックナンバーにある「フリーブロック
の検証」をじっくり読み返してほしい。

<ビットマップ・インデックス>

********************** カーディナリティ4の場合 ***********************

SQL> CREATE BITMAP INDEX BITMAP_4 ON T10MAN_COPY_4(MGR)
     STORAGE (INITIAL 2K NEXT 2K PCTINCREASE 0 MAXEXTENTS UNLIMITED) ;

SQL> SELECT SEGMENT_NAME,BLOCKS,EXTENTS FROM DBA_SEGMENTS
     WHERE SEGMENT_NAME = 'BITMAP_4' ;

SEGMENT_NAME            BLOCKS   EXTENTS
-------------------- --------- ---------
BITMAP_4                    90        89

********************* カーディナリティ100の場合 **********************

SQL> CREATE BITMAP INDEX BITMAP_100 ON T10MAN_COPY_100(MGR)
     STORAGE (INITIAL 2K NEXT 2K PCTINCREASE 0 MAXEXTENTS UNLIMITED) ;

SQL> SELECT SEGMENT_NAME,BLOCKS,EXTENTS FROM DBA_SEGMENTS
     WHERE SEGMENT_NAME = 'BITMAP_100' ;

SEGMENT_NAME            BLOCKS   EXTENTS
-------------------- --------- ---------
BITMAP_100                 205       204

******************** カーディナリティ100000の場合 ********************

SQL> CREATE BITMAP INDEX BITMAP_100000 ON T10MAN_COPY_100000(MGR)
     STORAGE (INITIAL 2K NEXT 2K PCTINCREASE 0 MAXEXTENTS UNLIMITED) ;

SQL> SELECT SEGMENT_NAME,BLOCKS,EXTENTS FROM DBA_SEGMENTS
     WHERE SEGMENT_NAME = 'BITMAP_100000' ;

SEGMENT_NAME            BLOCKS   EXTENTS
-------------------- --------- ---------
BITMAP_100000             1779      1639

ビットマップ・インデックスは、B*Treeインデックスとは異なり、カーディナ
リティが低ければ低いほど、格納スペースが小さくて済む。裏を返せば、カー
ディナリティが高ければ高いほど、その分ビットマップ・セグメントが必要と
する領域が大きくなってしまうと言えよう。
このことが理由で、カーディナリティが100000の場合のビットマップ・インデッ
クスは、B*Treeインデックスの格納スペースよりも多くの領域を必要としてし
まった。

これらの検証結果を基に、以下に特徴をまとめてみた。

1.
ビットマップ・インデックスは、カーディナリティが低いほど収納効率が良い
ので、カーディナリティが低い列の値でもパフォーマンス上の効果が得られる
可能性がある。しかし、例えば、性別を表わすカラムに、均等に「男性」と
「女性」が格納してある場合、それだけでWHERE句で性別 = ‘男性’のように絞っ
たとしても、検索は速くならないであろう。確かに、ビットマップ・インデッ
クス自体のアクセスは速くなるが、そこから取得したROWIDを基に、テーブルに
対して全体の50%ものデータを取得しに行ってしまうからである。

2.
カーディナリティが非常に高い(主キーに相当するようなカラム)と、かえっ
てB-Treeインデックスよりも収納効率が悪くなってしまう。言い換えれば、カー
ディナリティが高い場合は、B-Treeインデックスの方が向いているといえる。

構造の違いを理解していただけたであろうか?
次回は、このビットマップの構造が生み出す、ロックの問題に関して迫ってみ
る。

以上 茅ヶ崎にて