Difference between B-tree and Bitmap index.
In the world of Oracle Database tuning, choosing between a B-tree and a Bitmap index is one of the most consequential decisions you can make. While both are designed to speed up queries, they function in completely different ways and are meant for entirely different environments.
Think of it as the difference between a Library Catalog (B-tree) and a Simple Checklist (Bitmap).
The B-tree (Balanced Tree) is the default index in Oracle. It is designed to find a specific needle in a massive haystack.
Structure: It’s a tree of "branches" and "leaves." Each leaf contains a specific data value and a ROWID (the physical address) of the corresponding row.
Best For: High Cardinality columns—columns where most values are unique or have many distinct entries (e.g., EMPLOYEE_ID, EMAIL, SOCIAL_SECURITY_NUMBER).
The Logic: It excels at "equals" ($=$) and "range" ($>$, $<$, BETWEEN) searches.
A Bitmap index uses a string of bits ($0$s and $1$s) to represent the presence or absence of a value for every row in the table.
Structure: Oracle creates one bitmap for every unique value in the column. For a GENDER column, there would be one bitmap for 'M' and one for 'F'.
Best For: Low Cardinality columns—columns with very few unique values (e.g., GENDER, MARITAL_STATUS, REGION, YES_NO_FLAGS).
The Logic: It is incredibly fast at combining multiple filters using Boolean logic (AND, OR, NOT).
| Feature | B-tree Index | Bitmap Index |
| Ideal Cardinality | High (Many unique values) | Low (Few unique values) |
| Storage Size | Large (Grows with number of rows) | Tiny (Very compressed) |
| Environment | OLTP (Transactions/Updates) | Data Warehouse (Reporting) |
| Locking Level | Row-level (Very granular) | Block-level (Aggressive) |
| Performance | Best for single-row lookups. | Best for complex multi-column filters. |
| NULL values | Usually ignores NULLs. | Includes NULLs in the index. |
The biggest reason you cannot simply swap one for the other is how they handle Updates (DML).
B-tree: When you update a row, Oracle only locks that specific row in the index. This makes it perfect for "Busy" databases with thousands of users changing data at once.
Bitmap: Because of how bitmaps are compressed, updating one row effectively locks a huge chunk of rows (thousands at a time). If multiple users try to update a table with a bitmap index simultaneously, the database will grind to a halt due to "enq: TX - row lock contention."
You are searching for a specific record (e.g., "Find customer #987").
The data is constantly being inserted, updated, or deleted.
The column has many unique values.
You are building a Data Warehouse or an analytical system.
The data is mostly "Read-Only" (e.g., loaded once a night).
You frequently run queries with multiple AND/OR conditions, like:
WHERE region = 'West' AND status = 'Active' AND gender = 'F'
In a Data Warehouse, Bitmap indexes are magic because Oracle can perform bitwise operations in the CPU's registers. This allows it to compare millions of rows in milliseconds. However, if you see a Bitmap index on a PRIMARY KEY, that is a major red flag—it should almost always be a B-tree!