Execution plans related to bitmap indexing

A bitmap index is a type of index in an Oracle database other than the B-tree index, which is used primarily for data warehouses or DSS systems. In a data warehouse or DSS system, a bitmap index is much faster than a B-tree index for some types of SQL, mainly because the bitmap index implements a fast bitwise operation.

The physical storage structure of a bitmap index is similar to the physical storage structure of an ordinary B-tree index, and is stored in an orderly manner by the indexed key-value column, except that the index key value is not the only rowid that corresponds to the index key value, but instead becomes the combination of three parts. These three parts are corresponding to the lower bound of the ROWID, corresponding to the upper limit of rowid and compressed stored bitmap (Bitmap Segment, the bitmap segment can only be a bitmap index of the size of the leaf block 1/2), that is, the physical storage structure of the bitmap index in the Oracle database:< the key value of the index, corresponding to the lower bound of the ROWID, corresponding to the upper limit of the rowID, Bitmap section, the bitmap segment is compressed storage, after decompression is a series of 0 and 1 of the bits graph sequence, where 1 means that the index key value of a valid rowid,oracle through a conversion function (mapping function) Converts the 1 of the fragment of the decompressed segment to the upper and lower bounds of the corresponding rowid, to the valid ROWID corresponding to the index key value.

The physical storage structure of the bitmap index above determines that the lock granularity of the bitmap index in the Oracle database is on the bitmap segment of the index row. For the bitmap index of an Oracle database, it is the concept of no row lock, to lock the entire bitmap of the index row, and multiple rows of data may correspond to a bitmap segment of the same index. The granularity of this lock determines that the bitmap index does not apply to OLTP systems that are highly concurrent and frequently modified, and if bitmap indexing is used in highly concurrent and frequently modified OLTP systems, it is likely to cause serious concurrency problems and even create deadlocks.

Let's take a look at an example of a deadlock that occurs because of a bitmap index that causes common concurrent insert operations. Create a test table T1:

sql> CREATE TABLE t1 (ID number,sex char (20));

Table created.

Insert 10,000 data into the T1 table:

Sql> begin

2 for I in 1..5000 loop

3 INSERT INTO T1 values (i, ' MALE ');

4 INSERT into T1 values (i, ' FEMALE ');

5 end Loop;

6 end;

7/

PL/SQL procedure successfully completed.

Create a bitmap index on T1 's column sex IDX_BITMAP_T1:

Sql> Create bitmap index idx_bitmap_t1 on t1 (sex);

Index created.

Now let's construct the deadlock scenario, first insert a record in session 1 but not commit:

sql> INSERT INTO T1 values (10001, ' MALE ');

1 row created.

Next to Session 2, insert a record but do not commit:

sql> INSERT INTO T1 values (10002, ' FEMALE ');

1 row created.

Back to session 1, and then insert a record, this insert operation hang:

sql> INSERT INTO T1 values (10003, ' FEMALE ');--hang Live

Then we go back to session 2 and insert a record, and this insertion also hang:

sql> INSERT INTO T1 values (10004, ' MALE ');

The 2nd time back to session 1, where Oracle has detected a deadlock:

sql> INSERT INTO T1 values (10003, ' FEMALE ');

INSERT into T1 values (10003, ' FEMALE ') *

ERROR at line 1:

Ora-00060:deadlock detected while waiting for resource

The principle here is that when a record is inserted, Oracle needs to maintain a 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 the 5,000 records of the sex value male correspond to the same index row , the 5,000 records with the sex value of female are also the same index row. This also means that when inserting a new sex value for the 5,000 records of male, but when inserting a new record with the sex value of female, Oracle locks all of the previous 5,000 records that have a sex value of female (that is, the 5,000 records that have all sex values female), so a deadlock will normally occur for concurrent insert operations where there is no deadlock.

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

(1) because bitmap-indexed bitmap segments are stored after compression, the bitmap index segment is significantly more space-efficient than the B-tree index on the same column if the index has fewer distinct values. For example, the sex column of table T in the previous example, whose distinct value is only 2, although table T1 has a data volume of 10000, but the sex column has only two index rows in the single-key bitmap index idx_bitmap_t, and if a single-key-value B-Tree index is created on the sex column, It is clear that the number of index rows in the B-Tree index will be 10000.

(2) If you need to create an index on more than one column, the bitmap index tends to save significantly more storage space than the B-tree index under the same conditions. For example, for the three columns of master_status,region and gender on the table customer, the user may use any single column or columns in the above three columns to access the table customer, if a B-tree index is to be built at this time, Then you need to build three composite B-Tree index (here, considering that the composite B-Tree index can replace the single-key B-Tree index, the three-column composite B-Tree index can replace the two-column composite B-tree index) to cover all the situation, and if it is to build a bitmap index, then only need to build three of the three columns

(3) Bitmap indexing can quickly handle some SQL that contains a variety of and or or query conditions, mainly because bitmap indexing enables quick bitwise operations.

We use an example to illustrate the principle that bitmap indexing can achieve fast bitwise operations. Create a test table customer:

Sql> CREATE TABLE Customer (customer# number,marital_status varchar2, Region VARCHAR2, gender varchar2 (10), Income_level VARCHAR2 (10));

Table created.

Insert 6 records using the following sql:

Sql> INSERT into customer values (101, ' single ', ' East ', ' Male ', ' bracket_1 ');

1 row created.

Sql> INSERT into the customer values (102, ' married ', ' central ', ' female ', ' bracket_4 ');

1 row created.

Sql> INSERT into customer values (103, ' married ', ' West ', ' female ', ' bracket_2 ');

1 row created.

Sql> INSERT into customer values (104, ' divorced ', ' West ', ' Male ', ' bracket_4 ');

1 row created.

Sql> INSERT into customer values ("single", ' Central ', ' female ', ' bracket_2 ');

1 row created.

Sql> INSERT into the customer values (106, ' married ', ' central ', ' female ', ' bracket_3 ');

1 row created.

Sql> commit;

Commit complete.

Create a bitmap index on the column region idx_b_region

Sql> Create bitmap index idx_b_region on customer (region);

Index created.

Create another bitmap index on the column Martial_status idx_b_martialstatus:

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

Female Bracket_2

106 married central female Bracket_3

6 rows selected.

Execution plans related to bitmap indexing