ロックに関する検証 その５
～ロックに関する検証 その５～
ペンネーム ちゃむ
前回は、インデックスが作成してあるテーブルに対して更新処理を行ったとき
の、トランザクションロックに関しての検証を行った。
今回からは、ビットマップ・インデックスを作成したテーブルに対して、更新
処理を行なってみる。その時のロックの仕組みを検証する前に、ビットマップ・
インデックスの構造を十分に理解する必要がある。
＜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インデックスの格納スペースよりも多くの領域を必要としてし
まった。
これらの検証結果を基に、以下に特徴をまとめてみた。
１．
ビットマップ・インデックスは、カーディナリティが低いほど収納効率が良い
ので、カーディナリティが低い列の値でもパフォーマンス上の効果が得られる
可能性がある。しかし、例えば、性別を表わすカラムに、均等に「男性」と
「女性」が格納してある場合、それだけでWHERE句で性別 = ‘男性’のように絞っ
たとしても、検索は速くならないであろう。確かに、ビットマップ・インデッ
クス自体のアクセスは速くなるが、そこから取得したROWIDを基に、テーブルに
対して全体の50%ものデータを取得しに行ってしまうからである。
２．
カーディナリティが非常に高い（主キーに相当するようなカラム）と、かえっ
てB-Treeインデックスよりも収納効率が悪くなってしまう。言い換えれば、カー
ディナリティが高い場合は、B-Treeインデックスの方が向いているといえる。
構造の違いを理解していただけたであろうか？
次回は、このビットマップの構造が生み出す、ロックの問題に関して迫ってみ
る。
以上 茅ヶ崎にて