Oracle bitmap index B-Tree Index

Oracle bitmap index B-tree index is a common index in Oracle. It is the default index type when creating an index. A maximum of 32 columns can be included. The bitmap index Oracle creates a bitmap for each unique key and saves the ROWID associated with the key value as a bitmap. A maximum of 30 columns can be included. Generally, most users only create B-tree indexes with the NORMAL TYPE. Therefore, we do not create indexes for columns with a lower base, because B-tree indexes do not necessarily improve the query speed, and even increase the time consumed by the Insert, Update, and Delete commands. The following is an example to compare the query speed improvement of B-tree indexes and bitmap indexes. SQL> -- for example, the following test table contains 534888 SQL records> select count (*) from henry_test; COUNT (*) ---------- 534888 SQL> -- no index (1.631 seconds) SQL> select count (*) from henry_test where payment_method <'91 '; COUNT (*) ---------- 371466 SQL> -- create B-tree index SQL> create index idx_henry_test1 on henry_test (payment_method ); index createdSQL> -- use the prompt to force the use of index SQL> select/* + Index (henry_test IDX_HENRY_TEST1) */count (*) from henry_test where payment _ Method <'91'; COUNT (*) ---------- 371466SQL> -- (0.181 seconds) SQL> -- delete index SQL> drop index IDX_HENRY_TEST1; index dropped SQL> -- because the base of the payment_method column is very low, there are only five values, therefore, bitmap index SQL> -- create bitmap index SQL> create bitmap index IDX_HENRY_TEST1 on HENRY_TEST (PAYMENT_METHOD ); index created SQL> -- use the prompt to force the use of index SQL> select/* + Index (henry_test IDX_HENRY_TEST1) */count (*) from henry_test where payment_method <'91 '; COUNT (*) --------- -371466SQL> -- (Time consumed: 0. 01 seconds) SQL> -- delete index SQL> drop Index IDX_HENRY_TEST1; index dropped you can see that Bitmap indexes are more than 10 times faster than B-tree indexes. I copied the following two paragraphs directly from the book. Bitmap indexes are usually better than B-tree indexes when loading tables (insert operations. Generally, bitmap indexes are three to three faster than B-tree indexes with a low base (rarely different values ~ 4 times, but if the new value occupies more than 70% of the inserted rows, the B-tree index is usually faster. When each record adds a new value, the B-tree index is three times faster than the bitmap index. We recommend that you do not use Bitmap indexes in some online transaction processing (OLTP) applications. The index value of the B-Tree Index contains ROWID, so that Oracle can lock the index at the row level. Bitmap indexes are stored as compressed index values, which contain rowids in a range. Therefore, ORACLE must lock rowids in all ranges for a given value. This lock may cause deadlocks in some DML statements. The SELECT statement is not affected by this locking problem. Bitmap indexes have many restrictions: 1. The rule-based optimizer does not consider Bitmap indexes. 2. When the atler table statement is executed and the column containing the bitmap index is modified, make the bitmap index effective. 3. The bitmap index stores the index key value in the index block. However, they cannot perform any type of integrity check. 4. The bitmap index cannot be declared as a unique index.