Oracleのビットマップインデックスを学ぶ - kagamihogeのblogでは、基本的な使い方とその構造について学習した。次に、一般的な解説として、ビットマップインデックスは更新時にビットマップ全体をロックするのでオンライン向けの機能では無い、とされることが多い。そのため、このエントリの目的としては、ロックが発生する状況を試し、その解説が具体的にどういうことを指しているのか、を確認する。
環境
- DB
- ツール
物理的な格納構造の基本的な考え方
まず、Oracleのビットマップインデックスが物理的にはどのように格納されるのか、について。学習の情報源はこのエントリ末尾に一覧をまとめたのでそちらを参照して頂きたく。
重要なのは、ビットマップインデックスのリーフノードは、"カラムの値+ 開始ROWID + 終了ROWID + ビットマップ値"となる点である。ビットマップインデックスの内部的な格納構造はBTreeインデックスと同じである。ルートがあって、場合によってブランチがあり、リーフノードがある。ただし、リーフノードに保存される内容がBTreeとは異なる。
ロックの観点でビットマップとBTreeとを比較して重要なのは、ビットマップの方が常により広範囲をカバーする点である。詳細は参考URLに譲るが、ビットマップは一つのリーフノードで数百~数万のROWIDを管理し、ロックはその範囲に対してかかるので、たやすく競合が起こりやすい。
比較的簡単にロック待ちが発生する例
以上の点を踏まえて、実際にロックが発生する例を確認する。
CREATE TABLE bitmap_test_table_1(column1 INTEGER); CREATE BITMAP INDEX bitmap_test_table_index_1_1 ON bitmap_test_table_1 (column1); --NG INSERT INTO bitmap_test_table_1(column1) VALUES (0); INSERT INTO bitmap_test_table_1(column1) VALUES (0);
上記のINSERTはそれぞれのセッションが同一のリーフノードをロックしようとするので、後発セッションはロック待ちになる。
なお、以降のSQL例で上記のように二つのDML文を載せている場合、それぞれ別セッションのトランザクションで実行するもの、とする。コメント文がOK
であればロック待ちは発生せず、NG
では待ちが発生する。
ロック状況を確認してみる。
SELECT SID, TYPE, id1, id2, lmode, request FROM v$lock WHERE SID IN (14, 22) AND TYPE IN ('TM' ,'TX') ORDER BY SID;
以下のようにロック待ちが発生しているのが分かる。
SID | TYPE | ID1 | ID2 | LMODE | REQUEST |
---|---|---|---|---|---|
14 | TM | 70951 | 0 | 3 | 0 |
14 | TX | 65557 | 948 | 6 | 0 |
22 | TX | 262171 | 888 | 6 | 0 |
22 | TM | 70951 | 0 | 3 | 0 |
22 | TX | 65557 | 948 | 0 | 4 |
上記のように、特に問題無さそうなDML文であっても、ビットマップインデックスが存在するといともたやすくロック待ちが発生することが分かる。
以降では、INSERT, UPDATE, DELETEそれぞれについてもう少し詳しい挙動を確認する。
INSERT
まずテスト用のテーブルにデータを入れてビットマップインデックスを作成する。テストデータは100万件で、ビットマップインデックスの列は1,2,3,0
の4種類が繰り返し出現する。
CREATE TABLE bitmap_test_table_2 ( KEY INTEGER NOT NULL , column1 INTEGER , CONSTRAINT bitmap_test_table_2_pk PRIMARY KEY ( KEY ) ENABLE ); INSERT /*+ APPEND */ INTO bitmap_test_table_2(KEY, column1) SELECT ROWNUM, mod(ROWNUM, 4) FROM (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000), (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000); COMMIT; CREATE BITMAP INDEX bitmap_test_table_index_2_1 ON bitmap_test_table_2 (column1);
以上の状態のテーブルに対し、様々な条件のDMLを発行し、ロックが発生するかどうかを確認する。
まず、以下のINSERTは同一カラム値のブロックに対して追加しようとするので、ロック待ちが発生する。
--NG INSERT INTO bitmap_test_table_2(KEY, column1) VALUES (1000001, 0); INSERT INTO bitmap_test_table_2(KEY, column1) VALUES (1000002, 0);
ただし、各INSERTが異なるカラム値であれば、それぞれのセッションは異なるブロッをロックするので待ちは発生しない。
--OK INSERT INTO bitmap_test_table_2(KEY, column1) VALUES (1000001, 0); INSERT INTO bitmap_test_table_2(KEY, column1) VALUES (1000002, 1);
UPDATE
UPDATEも、リーフノードにカラム値プラスROWIDの範囲がありそのブロックに対してロックがかかる、が原則になる。よって、どのような時にロックが発生するかはこの原則を理解しているかがカギとなる
……のだけど。UPDATE文だけを見ていると直感的に反するロックが発生するので、ぶっちゃけ俺は理解するまでに尋常ならざる時間がかかったことを先に書いときます。
テストデータはINSERTのときに挙げたものと同じものを使用する。なお、この項で使用する範囲のデータは以下の通り。
SELECT * FROM bitmap_test_table_2 WHERE KEY IN (1, 3, 5, 22897, 22901, 50001);
KEY | COLUMN1 |
---|---|
1 | 1 |
3 | 3 |
5 | 1 |
22897 | 1 |
22901 | 1 |
50001 | 1 |
まず、お互いのセッションが同じブロックを更新しようとすれば当然ロック待ちが発生する。以下はカラム値が1と2の両方ともが被っているためNGである。
--NG column1 1 -> 2, 1 -> 2 UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 1; UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 5;
以下もNG。
--NG column1 1 -> 2, 1 -> 3 UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 1; UPDATE bitmap_test_table_2 SET column1 = 3 WHERE KEY = 5;
ただし、以下は問題無い。カラム値を1から2、3から0なので、更新するブロックが被っていない。
--OK column1 1 -> 2, 3 -> 0 UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 1; UPDATE bitmap_test_table_2 SET column1 = 0 WHERE KEY = 3;
それでは、更新するカラム値が被っていると常にダメかというとそうでもない。以下は、それぞれのセッションがカラム値1から2に更新しているが、ロック待ちは発生しない。
--OK column1 1 -> 2, 1 -> 2 UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 1; UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 50001;
以下もOK。
--OK column1 1 -> 2, 1 -> 2 UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 1; UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 22901;
しかし、以下はNGとなる。
--NG column1 1 -> 2, 1 -> 2 UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 1; UPDATE bitmap_test_table_2 SET column1 = 2 WHERE KEY = 22897;
この理由はビットマップインデックスの構造で説明がつく。ビットマップインデックスのリーフノードは、カラム値と「ある範囲のROWID」で構成されている。カラム値が同一の行であっても、二つのROWIDの距離が十分遠ければ、単一ブロックの「ある範囲のROWID」に収まらなくなる。この場合、それぞれのROWIDは別々のブロックに格納されるので、ロックも別々のブロックに対して発生し、待ちは発生しない。
上記の例で言うと、column1のKEY=1から22897は一つのブロックなので、待ちが発生する。しかし、22901以降は別のブロックなので待ちが発生しない。
DELETE
DELETEもUPDATEと同様の考え方なので詳細は省略し、SQL例と待ちが発生するかどうかだけに留める。
--NG DELETE bitmap_test_table_2 WHERE KEY = 1; DELETE bitmap_test_table_2 WHERE KEY = 5;
--OK DELETE bitmap_test_table_2 WHERE KEY = 1; DELETE bitmap_test_table_2 WHERE KEY = 3;
--OK DELETE bitmap_test_table_2 WHERE KEY = 1; DELETE bitmap_test_table_2 WHERE KEY = 50001;
まとめ
追加更新削除が頻繁に発生する列にビットマップインデックスを作成してはいけない(戒め)
異常にややこしいのでこのエントリをまとめるとしたら「ビットマップインデックスは基本的に行ロックが不可能。また、数千~数万の範囲のROWIDをロックするため、追加・更新・削除の同時実行にマッタク向いていない」といったところ。一般的に、ビットマップインデックスはオンラインに不向きと言われるが、そのことが確認出来たと思う。
ごくごく例外的なケースを除き、ビットマップインデックス列に対する更新操作はロック待ちが発生する。このロックは実際のクエリだけを見ると、まず間違いなく不可解なロックなので、本番環境では深刻なパフォーマンス障害のタネになりかねない。
可能なケースがあるならそれでいいじゃないという意見もあるかもしれないが、俺としては反対である。ビットマップインデックスはその構造からしてそもそもオンライン向きではなく、更新を犠牲にして低カーディナリティ列の検索性能を上げることに特化した代物である。不向きな環境で無理に使用するのは、一般的には利益よりも害の方が大きいもんである。