Comparative study on lock cost of "Bitmap index" B-tree index and Bitmap bitmap index

Use the following experiment to verify that the bitmap bitmap index is a "high cost" compared to a normal B-tree index lock. Bitmap indexing brings up a "bitmap segment level lock", and the actual use process must be fully aware of the locking cost of different indexes.

1. To compare differences, create a test table of two index types
1) Create a bitmap index on table T_bitmap
[Email protected]> CREATE TABLE T_bitmap (ID number), name VARCHAR2 (ten), Sex VARCHAR2 (1));

Table created.

[Email protected]> create bitmap index t_bitmap_idx on t_bitmap (sex);

Index created.

2) Create a normal B-tree index on table T_btree
[Email protected]> CREATE TABLE T_btree (ID number), name VARCHAR2 (ten), Sex VARCHAR2 (1));

Table created.

[Email protected]> CREATE index t_btree_idx on t_btree (sex);

Index created.

2. Initialize two data per table: "A Boy" and "A Girl"
Comments:
M-male-means the boy;
F-femail-Represents the girl.

1) Initializing Data t_btree table data
[Email protected]> INSERT INTO t_btree values (1, ' Secoooler ', ' M ');

1 row created.

[Email protected]> INSERT INTO t_btree values (2, ' Anna ', ' F ');

1 row created.

2) Initializing Data t_bitmap table data
[Email protected]> INSERT INTO t_bitmap values (1, ' Secoooler ', ' M ');

1 row created.

[Email protected]> INSERT INTO t_bitmap values (2, ' Anna ', ' F ');

1 row created.

[Email protected]> commit;

Commit complete.

3) View the results after initialization
(1) The T_btree table contains two data
[Email protected]> select * from T_btree;

ID NAME S
---------- ---------- -
1 Secoooler M
2 Anna F

(2) The T_bitmap table contains two data
[Email protected]> select * from T_bitmap;

ID NAME S
---------- ---------- -
1 Secoooler M
2 Anna F

3. In two different sessions, insert, modify, and delete "Boy" data for T_btree demo with normal B-tree index table
Do not commit after inserting in the first session
[Email protected]> INSERT INTO t_btree values (3, ' Andy ', ' M ');

1 row created.

Insert the same state data in the second session, and you can see that the insert, modify, and delete can be completed successfully
[Email protected]> INSERT INTO t_btree values (4, ' Tutu ', ' M ');

1 row created.

[Email protected]> update t_btree set sex= ' M ' where id=2;

1 row updated.

[Email protected]> Delete from T_btree;

2 rows deleted.

4. Insert, modify, and delete "Boy" data in two different sessions with bitmap bitmap Index Table T_bitmap Demo
1) do not commit after inserting in the first session
[Email protected]> INSERT INTO t_bitmap values (3, ' Andy ', ' M ');

1 row created.

2) The second session of the Boy data processing, you can see, as long as the operation information involved in the bitmap index column insert, modify and delete can not be completed!!
(1) Insert Test
When inserting data involves the bitmap index column "Sex" field, it is not possible to complete.
[Email protected]> INSERT INTO t_bitmap values (4, ' Tutu ', ' M ');
The problem arises: there is a "lock waiting" phenomenon of stagnation!

You can do this when the insert data does not involve the bitmap index column "Sex" field.
[Email protected]> INSERT INTO t_bitmap (id,name) VALUES (4, ' Tutu ');

1 row created.

[Email protected]> commit;

Commit complete.

(2) Update test
At this point the test data for the second session is as follows.
[Email protected]> select * from T_bitmap;

ID NAME S
---------- ---------- -
1 Secoooler M
2 Anna F
4 Tutu

When updating the bitmap index column the "Sex" field has a value of "M", it cannot be completed.
[Email protected]> update t_bitmap set sex= ' M ' where id=1;

1 row updated.

This succeeds because the sex value of the first row of data is itself "M".

[Email protected]> update t_bitmap set sex= ' M ' where id=2;
The problem arises: there is a "lock waiting" phenomenon of stagnation!

[Email protected]> update t_bitmap set sex= ' M ' where id=4;
The problem arises: there is a "lock waiting" phenomenon of stagnation!

In addition, it is important to note that if the updated column is not the corresponding column for the bitmap index, it will not be restricted by the bitmap segment-level index lock. as shown below.
[Email protected]> update t_bitmap set name= ' Xu ' where id=2;

1 row updated.

(3) Delete test
When the deleted data contains the bitmap index column the "Sex" field has a value of "M", it cannot be completed.
[Email protected]> Delete from t_bitmap where id=1;
The problem arises: there is a "lock waiting" phenomenon of stagnation!
When you delete all the data in a table, the same is true and cannot be deleted.
[Email protected]> Delete from T_bitmap;
The problem arises: there is a "lock waiting" phenomenon of stagnation!

5. Summary
In this paper, we demonstrate the locking cost of b-tree and bitmap indexes, taking the insert action with the least impact force of the data itself as an example. For B-tree indexes, the Insert action does not affect DML operations for other sessions, but for the bitmap index, because it is an index segment level lock, the content associated with the Action column value is locked (the "M" information mentioned in this article). Further, for the update action,

Causes of the above phenomenon:
The bitmap index is stored as a compressed index value that contains a range of ROWID, so Oracle must lock all ranges of rowid for a given value and does not support row-level locking.
Another way to describe this: When using a bitmap index, a key points to multirow (hundreds of thousands), and if you update a bitmap index key, the other rows corresponding to the bitmap index field will be locked!

Advantages compared to B-tree indexes:
Bitmaps are stored in a compressed format, and therefore occupy much less disk space than the B-tree index

Disadvantages compared to B-tree indexes:
The high cost of this lock can cause some DML statements to "lock wait", severely affecting the efficiency of insertions, updates, and deletions, and not for highly concurrent systems.

Bitmap Index usage principles:
Bitmap indexes are primarily used for decision support systems or static data and do not support index row-level locking.
Bitmap indexes are best used for low cardinality columns (that is, the column's unique value is divided by the number of rows to a very small value, close to 0), such as the "Gender" column above, with the column values "M", "F" two. On the basis of this basic principle, we should seriously consider the operation characteristics of the table containing the bitmap index, if it is a system with high concurrency, it is not suitable to use the bitmap index!

Ext.: http://blog.itpub.net/519536/viewspace-611296/

Comparative study on lock cost of "Bitmap index" B-tree index and Bitmap bitmap index