Oracle Bitmap Indexing

The following is primarily about Oracle 10g bitmap indexing, with a focus on Storage, other advantages and disadvantages, query mechanism is also introduced slightly, because the storage mechanism is the foundation of . The
content is mainly divided into four parts:
1) from http://blog.chinaunix.net/uid-20687159-id-1894992.html
2) from http://blog.sina.com.cn/ S/blog_4c6fef63010085m8.html
3) comes from Oracle's official documentation and some others.   
4) Individual experiments, look at the bitmap index situation.
------------------------------
It is essential to understand bitmap indexing in order to ascend to the DW. The key to understanding a bitmap index is knowing the internal structure of the index, knowing the result, and naturally knowing its pros and cons, so understanding the structure is the core.

One, Oracle Official document
about bitmap index overview-Define, Query principle, advantages, disadvantages .
Here is just a note, the unofficial text, extracted the outline content:
1. Define, what is a bitmap index: the index represented by a bitmap, Oracle creates a bitmap for each key value of the selected bottom column, and each bit in the bitmap may correspond to more than one column. A bit equal to 1 in a bitmap indicates that a particular row contains the key value represented by this bitmap.
2. Query, because the index is a bitmap, so many times the bitmap in these indexes can be bitwise--(and and OR), which is significantly faster than the B-tree (in some cases). Because bitmap indexes can store null, they can be counted directly through the bitmap index (which is certainly accurate). The latter is a bit more directly related to how bitmaps are computed.
3. Advantages of bitmaps (mainly for DW):

• Reduce the time for ad hoc queries
• Real savings in index data space compared to other types of indexes
• Even on very poor hardware, there may be dramatic performance gains
• Efficient parallel DML and load operations.
• It is more efficient to build indexes, first to be unordered, and second to occupy less space (index space).
• Can be counted directly from a bitmap index.

4. The disadvantage of bitmap indexing (other information) is not good to say is a disadvantage

• Columns that are not suitable for the selection of the bottom
• If you have more frequent operations such as insert,update, this can cause performance to be very high, because the update cable refers to a row lock (which may lock multiple rows) rather than an exclusive lock.
• May overflow, index data blocks are difficult to lay down the entire index value, which results in inefficiency.

Two, http://blog.chinaunix.net/uid-20687159-id-1894992.html
-Logical Analysis Bitmap
Main to close to understand the next chart:

Note that this is only, in fact, the number of bits per bitmap is not exactly equal to the number of records, but will be decomposed according to the situation, otherwise, for the majority of the data, the bitmap is too large.

Three, http://blog.sina.com.cn/s/blog_4c6fef63010085m8.html
In-depth research-how data blocks are stored
This is really in-depth understanding.
For the sake of convenience, temporarily do not modify the original style

I. What is a bitmap index We currently use a large number of indexes are mainly B*tree index, in the index structure store the key value and the key value of the ROWID, and is one by one corresponding. The bitmap index is primarily created for columns of the same value (for example: category, operator, Department ID, warehouse ID, etc.), An index row in an index block stores the key values and the start and end rowID, and the location encoding of those key values, Each of the location codes represents the data row that corresponds to the key value. A bitmap index block might point to the location of dozens of or even hundreds of rows of data. This way of storing data, relative to the B*tree index, takes up very little space and is created and used very quickly. When querying based on key values, you can quickly locate data based on the starting rowid and bitmap states. When making And,or or in (x, y,..) based on key values When querying, directly with the bitmap of the index or operation to quickly obtain the result row data. When select count (XX), the index can be accessed directly to quickly derive statistics. Creating the syntax is simple, which is to add the keyword bitmap to the syntax of the normal index creation, for example: Create bitmap index H patient registration record _ix_ executor on H Patient Registration record (executive person); Two. Features of bitmap indexing Storage space for 1.Bitmap indexes With respect to the B*tree index, bitmap indexing takes up very little space because it stores only the starting and ending rowid and bitmaps of the key values. The space occupied by bitmap is mainly rooted in the following 4 factors: A. Total number of records in a table B. The number of key values for the indexed columns, and the fewer the different values for the columns, the fewer bitmaps are required. C. Type of operation, BULK insert is much less than a single insert of the bitmap, 8i,9i is such, 10G there is no such difference, see the following analysis. D. The physical distribution of the same key value in the index column, 8i,9i, the data on different blocks, the same key value, will establish a different bit row (segment) to represent Note: The 8i,9i,10g mentioned in this article, I am experimenting with the environment is 8.1.7,9.2.0.5,10.2 2.Bitmap Index creation speed Bitmap indexes are created without ordering and are stored in bits, requiring less space. B*tree indexes need to be sorted, positioned, and so on when they are created, much slower. 3.Bitmap index allows null key value B*tree index because null values are not logged, a full table scan is used when the is null-based query. An IS null query on a bitmap index column makes it possible to use an index. 4.Bitmap index efficient access to table records When you use COUNT (XX), you can quickly derive statistics by accessing the index directly. When And,or or in (x, y,..) based on the column of the bitmap index When queried, the bitmap is directly indexed or operated, and the data can be filtered beforehand before accessing the data. 5.Bitmap indexes only need to be indexed once for bulk DML operations Because the data is reflected by a bitmap, the batch operation is much faster to update the index than a row of B*tree indexes. Lock mechanism for 6.BITMAP indexing For B*tree indexes, the insert operation does not lock DML operations for other sessions. The bitmap index, because the bitmap reflects data, different sessions to update the same key value of the same bitmap segment, insert, UPDATE, delete each other operations will be locked. For Oracle 8i,9i, single-row inserts, because a bit row (bitmap segment) records only 8 rows of records, the DML operation that locks up to 8 rows of data of the same key value. In bulk inserts, like 10G, the same key value has only one bit row (bitmap segment), so DML operations for all data with the same key value are locked. Below, observe the locking mechanism for 8i,9i: Sql> Declare Begin For I in 1..9 Loop  Insert into H patient registration record (Id,no, number, performer) Values (i, ' G000001 ', 1, ' 1 '); End Loop; Commit; End; / sql> Delete h patient registration record where id=1; Do not commit, open another session, sql> Delete h patient registration record where id=9; The operation can proceed without locking. sql> Delete h patient registration record where id=8; The operation waits, because the bitmap index of the record with another session operation is on the same bitmap segment (up to 8 lines of a bitmap segment), so it is locked. Three. Where to use bitmap indexing 1. Bitmap indexing is an Oracle database that was added in version 7.3, 8i,9i Enterprise and personal support, and is not supported by the Standard Edition. 2. Rule-based optimizer cannot use bitmap index 3. Suitable for column queries with a large number of duplicate values 4. For 8i,9i version, does not apply to single-line insert, for BULK INSERT data,  because the same key value is inserted on a single line, each insert of 8 rows produces a bitmap segment in a row of index blocks, even if the same value.  When bulk INSERT, the same key value generates only one bitmap segment. 5. Because concurrent DML operations lock up a large number of rows of data for the entire bitmap segment, the bitmap index is primarily used for OLAP applications and can also be used for tables that are primarily read operations in OLTP. Two parameters for bitmap Sql> show parameter bitmap; NAMETYPEVALUE ------------------------------------ ----------- ------------------------------ Bitmap_merge_area_sizeinteger1048576 Create_bitmap_area_sizeinteger8388608 where bitmap_merge_area_size is the area of memory used by the bitmap index for merge operations , Create_bitmap_area_size is the area of memory used when creating. In the 8i,9i, you need to adjust the size of the bitmap and the common usage conditions. 9i or more, just set the value of the Pga_aggregate_target, and Oracle will automatically enter and adjust the memory. Four. Bitmap index storage principle The bitmap index stores each key value of a column in a data table as a single bitmap, and Oracle has different versions, different modes of operation, and data generation differences. For 8i,9i, The following 3 ways to discuss data insertion: A. Insert one row at a time, insert multiple lines, and commit once; B. Submit once for each line inserted; C. Bulk Insert method, one commit; for the first way, observe the changes in the bitmap index. A. Suppose inserting 8 rows of data of the same key value, if inserted in each line, and then committed at a time, generates 8 bitmaps sql> INSERT into H patient registration record (Id,no, number, performer) Values (1, ' G000001 ', 1, ' 1 '); 1 row inserted Sql>/ 1 row inserted Sql>/ 1 row inserted Sql>/ 1 row inserted Sql>/1 Row inserted sql>/ 1 row inserted Sql>/ 1 row inserted Sql>/ 1 row inserted Sql> c Ommit; Commit complete sql> alter system dump DATAFILE 1 block 40028; System altered row#0[7847] Flag:-----, lock:0 Col 0; Len 3; (3):   d5 c5--Key value ' Zhang 1 ' Col 1; Len 6; (6):9c (XX)the starting position of the--rowid Col 2; Len 6; (6):9cstop position of the--rowid Col 3; Len 2; (2):C8 FF--Bitmap encoding ROW#1[7802] Flag:-----, lock:0 Col 0; Len 3; (3):d5 c5 31 Col 1; Len 6; (6):9c 54 00 08 Col 2; Len 6; (6):9c 0f Col 3; Len 2; (2):C8 03 ROW#2[7780] Flag:-----, lock:0 Col 0; Len 3; (3):d5 c5 32 Col 1; Len 6; (6):9c 54 00 08 Col 2; Len 6; (6):9c 0f Col 3; Len 1; (1):02 ROW#3[7758] Flag:-----, lock:0 Col 0; Len 3; (3):d5 c5 33 Col 1; Len 6; (6):9c 54 00 08 Col 2; Len 6; (6):9c 0f Col 3; Len 1; (1):03 ROW#4[7736] Flag:-----, lock:2 Col 0; Len 3; (3):d5 c5 34 Col 1; Len 6; (6):9c 54 00 08 Col 2; Len 6; (6):9c 0f Col 3; Len 1; (1):04 ROW#5[7714] Flag:-----, lock:2 Col 0; Len 3; (3):d5 c5 35 Col 1; Len 6; (6):9c 54 00 08 Col 2; Len 6; (6):9c 0f Col 3; Len 1; (1):05 -----End of leaf block dump----- However, the next time you insert a row of data with the same key value, the 8 row bitmap is automatically merged into a single row of bitmaps, and a new index bitmap row is generated with the index of the row just inserted: Sql> Insert into H patient registration record (Id,no, number, performer) Values (1, ' G000001 ', 1, ' 1 '); 1 row inserted Sql> commit; Commit Complete sql> alter system dump DATAFILE 1 block 40028; System Altered ROW#0[7847] Flag:-----, lock:2 Col 0; Len 3; (3):d5 c5 31 Col 1; Len 6; (6):9c 54 00 00 Col 2; Len 6; (6):9c 54 00 07 Col 3; Len 2; (2):C8 FF ROW#1[7825] Flag:-----, lock:2 Col 0; Len 3; (3):d5 c5 31 Col 1; Len 6; (6):9c 54 00 08 Col 2; Len 6; (6):9c 0f Col 3; Len 1; (1):00 -----End of leaf block dump----- B. The data is submitted per line, similar to the above, but a little different, each commit a row, copy the original bitmap, generate a new bitmap, and mark the original bitmap as deleted, Bitmaps that are marked as deleted will only clear bitmaps that are marked as deleted and reuse them only if the index block needs to be assigned a new bitmap. The results of the experiment on 8i,9i are consistent with Itpub's <oracle database performance Optimization > Book 378 page. If 1000 data of the same key value is inserted, 125 bitmap rows, including 8 records, are generated. C. Third Way, BULK insert data, INSERT INTO H patient registration record (ID,NO, number, executor) SELECT * * *,  The same key value, only one bitmap is generated at a time. Sql> Insert into H patient registration record (ID,NO, number, executor) Select 1, ' G000001 ', 1, ' 1 ' from dual Union All Select 2, ' G000002 ', 1, ' 1 ' from dual Union All Select 3, ' G000003 ', 1, ' 1 ' from dual Union All Select 4, ' G000004 ', 1, ' 1 ' from dual Union All Select 5, ' G000005 ', 1, ' 1 ' from dual Union All Select 6, ' G000006 ', 1, ' 1 ' from dual Union All Select 7, ' G000006 ', 1, ' 1 ' from dual Union All Select 8, ' G000006 ', 1, ' 1 ' from dual Union All Select 9, ' G000006 ', 1, ' Zhang 1 ' from dual; Sql> commit; Commit Complete sql> alter system dump DATAFILE 1 block 40028; System Altered ROW#0[8006] Flag:-----, lock:2 Col 0; Len 3; (3):d5 c5 31 Col 1; Len 6; (6):9c 54 00 00 Col 2; Len 6; (6):9c 0f Col 3; Len 3; (3):C9 FF 01 ROW#1[8030] Flag:---D-, lock:2 Col 0; Null Col 1; Null Col 2; Null Col 3; Null -----End of leaf block dump----- Therefore, bitmap indexes are best used in bulk inserts, so that each key value generates only one bitmap. Instead of single-row data insertion, each key value generates a bitmap for every 8 rows of data. 10G is a much simpler situation. In the above 3 ways, the same key value is inserted, the bitmap generation is the same, only one bitmap, and, each time the commit, does not delete the previous bitmap, but directly modify the corresponding key value of the bitmap. Insert a row of data each time, insert 9 rows after submitting ROW#0[7763] Flag:------, lock:2, len=29 Col 0; Len 3; (3):d5 c5 31 Col 1; Len 6; (6):00 00 00 00 00 00 Col 2; Len 6; (6):EF F2 0f Col 3; Len 8; (8):F9 e4 d5 dc BC 01 FF -----End of leaf block dump----- BULK INSERT 9 rows of data and submit ROW#0[7733] Flag:------, lock:2, len=30 Col 0; Len 3; (3):d5 c5 31 Col 1; Len 6; (6):00 00 00 00 00 00 Col 2; Len 6; (6):EF F2 00 17 Col 3; Len 9; (9):fa e4 d5 dc BC 03 FF FF -----End of leaf block dump----- As you can see, 10G optimizes the storage of bitmap indexes, with a key value that has only one bitmap in the index block Some information about bitmap indexing can be found in: Bitmap a little inquiry into the http://www.itpub.net/114023.html Note that some of these conclusions are not entirely correct and can be proved by their own experiments, and the experiments involved in this article are not marked with the Oracle version, different versions, and the results differ