Bitmap Indices

Bitmap indexes in Oracle Database can be a powerful tool in SQL Tuning, enabling efficient access to data in certain circumstances. They provide an alternative to traditional B-Tree indexes, which are used in most database systems. A Bitmap index in Oracle uses a bitmap for each key value instead of a list of rowids. Each bit in the bitmap corresponds to a possible rowid: if the bit is set, it means that the row with the corresponding rowid has the key value. When Oracle uses a Bitmap index, it first retrieves the bitmap for the key value it is looking for, then uses a fast algorithm to convert the bitmap into a list of rowids. These rowids can then be used to quickly retrieve the corresponding rows from the table. Bitmap indexes are particularly effective in situations where the indexed column has a low cardinality, meaning it has relatively few distinct values compared to the number of rows in the table. Examples might include a 'gender' column (with possible values 'M' and 'F') or a 'marital status' column (with possible values 'Single', 'Married', 'Divorced', 'Widowed'). The more skewed the data distribution, the more effective the bitmap index.
Another major strength of Bitmap indexes is their ability to handle complex Boolean queries involving multiple conditions (AND, OR, NOT). Oracle can take multiple bitmaps (one for each condition) and perform fast bitmap operations to combine them into a result bitmap. This is much more efficient than having to scan the table multiple times, once for each condition, as you might have to do with a B-Tree index. However, Bitmap indexes do have some drawbacks and limitations. They are less efficient than B-Tree indexes when the data distribution is not skewed and the number of distinct key values is high. Moreover, they can cause contention issues in a multi-user environment where there are concurrent DML (Data Manipulation Language) operations, as multiple sessions may try to update the same bitmap at the same time. To sum up, Bitmap indexes in Oracle Database can greatly improve query performance in specific circumstances, particularly for low-cardinality columns and complex Boolean queries. However, they need to be used with care, taking into account their potential downsides. As always, it is important to thoroughly test any indexing strategy to ensure it delivers the expected benefits.

A bitmap index is a special type of index that uses a series of bitstrings to represent the set of ID values that correspond to a given indexed data value. You can define a bitmap index for a field if the table's ID field is defined as a positive integer (see restrictions).
Bitmap indices have the following important features:

1. Bitmaps are highly compressed: bitmap indices can be significantly smaller than standard indices. This reduces disk and cache usage considerably.
2. Bitmaps operations are optimized for transaction processing: you can use bitmap indices within tables with no performance penalty as compared with using standard indices.
3. Logical operations on bitmaps (counting, AND, and OR) are optimized for high performance.
4. The SQL Engine includes a number of special optimizations that can take advantage of bitmap indices.

Subject to the restrictions listed below, bitmap indices operate in the same manner as standard indices. Indexed values are collated and you can index on combinations of multiple fields.