Shulou(Shulou.com)06/01 Report--

Bitmap index is another type of index in oracle database besides B-tree index. It is mainly used in data warehouse or DSS system. In data warehouse or DSS system, bitmap index is much faster than B-tree index for some types of sql. This is mainly because bitmap index realizes fast bitwise operation.

The physical storage structure of the bitmap index is similar to that of the ordinary B-tree index, and it is stored sequentially according to the key value column of the index, except that the key value stored with the index key value is no longer just the rowid corresponding to the index key value, but becomes a combination of three parts. These three parts correspond to the lower bound of rowid, respectively, corresponding to the upper limit of rowid and the bitmap to be compressed (Bitmap Segment, the maximum bitmap segment can only be 1x2 of the leaf block size of bitmap index), that is, the physical storage structure of bitmap index in oracle database: here the bitmap segment is compressed, and the decompressed bitmap sequence is a series of binary bitmap sequences of 0 and 1, where 1 represents a valid rowid of the indexed key. Oracle converts 1 in the bitmap segment of the decompressed segment into a valid rowid corresponding to the indexed key value by combining the upper and lower bounds of the corresponding rowid through a conversion function (mapping function).

The physical storage structure of the above bitmap index determines that the locking granularity of the bitmap index in the oracle database is on the bitmap segment of the index row. For the bitmap index of oracle database, it does not have the concept of row lock. To lock the entire bitmap of the index row, multiple data rows may correspond to bitmap segments of the same index. The granularity of this lock determines that bitmap index is not suitable for highly concurrent and frequently modified OLTP systems. If bitmap indexes are used in highly concurrent and frequently modified OLTP systems, it is likely to lead to serious concurrency problems and even deadlocks.

Let's take a look at an example of deadlocks in common concurrent insert operations due to bitmap indexes. Create a test table T1:

SQL > create table T1 (id number,sex char (20))

Table created.

Insert 10000 pieces of data into the T1 table:

SQL > begin

2 for i in 1..5000 loop

3 insert into T1 values (iMagazine Male`)

4 insert into T1 values (iMagazine FEMALE`)

5 end loop

6 end

7 /

PL/SQL procedure successfully completed.

Create a bitmap index IDX_BITMAP_T1 on column SEX of T1:

SQL > create bitmap index idx_bitmap_t1 on T1 (sex)

Index created.

Now let's construct a deadlock scenario, first inserting a record in session 1 without commit:

SQL > insert into T1 values (10001)

1 row created.

Then go to session 2 and insert a record without commit:

SQL > insert into T1 values (10002jue FEMALE')

1 row created.

Go back to session 1, insert another record, and the insert operation hang stops:

SQL > insert into T1 values (10003 hang live in FEMALEI)

Go back to session 2, insert another record, and the insert operation also hang:

SQL > insert into T1 values (10004J. Maele)

Return to session 1 for the second time, where oracle has detected a deadlock:

SQL > insert into T1 values (10003jue FEMALE')

Insert into T1 values (10003 recording FEMALE') *

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

The principle here is that when inserting a record, oracle needs to maintain the bitmap segment of the corresponding key value in the bitmap index IDX_BITMAP_T1, because the number of records is small and oracle is the compressed storage bitmap segment, all 5000 records of sexuality male correspond to the same index row, and 5000 records of sexuality female correspond to the same index row. This also means that when inserting a new 5000 records with a sex value of male); but when inserting a new record with a sex value of female, oracle locks the bitmap segment corresponding to all the 5000 records with the sex value of female (that is, locking all the 5000 records with the sex value of female), so there is usually a deadlock for concurrent insert operations that do not have deadlocks.

Compared with B-tree index, the advantages of bitmap index are mainly reflected in the following aspects:

(1) because the bitmap segment of the bitmap index is stored after compression, if the difference value of the indexed bitmap is less, then the bitmap index segment will significantly save space compared with the B-tree index on the same column. For example, the SEX column of table T in the above example has a distinct value of only 2. Although table T1 has a data volume of 10000, there are only two index rows in the single key bitmap index idx_bitmap_t on the SEX column. If a single key value B-tree index is created on the sex column, it is obvious that the number of index rows in the B-tree index will be 10000.

(2) if you need to create indexes on multiple columns, then bitmap indexes tend to significantly save storage space compared with B-tree indexes under the same conditions. For example, for the three-column master _ status,region and gender on the table CUSTOMER, users may use any one or more of the above three columns to access the table customer. If you want to build a B-tree index, then you need to build three composite B-tree indexes (considering whether the composite B-tree index can replace the single-key value B-tree index, and the three-column composite B-tree index can replace the two-column composite B-tree index) in order to cover all cases. If you are building a bitmap index, you only need to build three single-key bitmap indexes for the above three columns.

(3) Bitmap index can quickly deal with some sql with various AND or OR query conditions, mainly because bitmap index can realize fast bitwise operation.

With regard to the principle that bitmap indexes can achieve fast bitwise operations, we use an example to illustrate. Create a test table customer:

SQL > create table customer (customer# number,marital_status varchar2 (10), region varchar2 (10), gender varchar2 (10), income_level varchar2 (10))

Table created.

Insert 6 records using the following sql:

SQL > insert into customer values (101 recordings, bracketbooks, 1')

1 row created.

SQL > insert into customer values (102 people, women, girls, bracketboys, etc.)

1 row created.

SQL > insert into customer values (103 grammes, bracketbooks, etc.)

1 row created.

SQL > insert into customer values (104, divorced1, 4', western, 4', malegal, 4')

1 row created.

SQL > insert into customer values (105 people, women, girls, brackets, etc.)

1 row created.

SQL > insert into customer values (106 people, women, girls, bracketboys, etc.)

1 row created.

SQL > commit

Commit complete.

Create a bitmap index idx_b_region on column region

SQL > create bitmap index idx_b_region on customer (region)

Index created.

Create another bitmap index idx_b_martialstatus on column martial_status:

SQL > create bitmap index idx_b_maritalstatus on customer (marital_status)

Index created.

SQL > select * from customer

CUSTOMER# MARITAL_ST REGION GENDER INCOME_LEV

--

101 single east male bracket_1

102 married central female bracket_4

103 married west female bracket_2

104 divorced west male bracket_4

105 single central female bracket_2

106 married central female bracket_3

6 rows selected.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.