Columns with few distinct values, such as a status, a cabin, or a region, are poor candidates for B-tree indexes but ideal for bitmap indexes. A bitmap index stores, for each distinct value, a bitmap with one bit per row. It is very small, answers counts directly, and combines with other bitmaps for complex conditions. Its weakness is concurrent changes.
Code for This Guide
The main examples are in the examples/indexes folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create bitmap index index on table (column [, ...]) [local]
Count Rows from the Bitmap
Example:
create bitmap index flights_status_bx on flights (status); explain plan for select count(*) from flights where status = 'CANCELLED'; select * from table(dbms_xplan.display(format => 'BASIC'));
Output:
Index FLIGHTS_STATUS_BX created. Explained. PLAN_TABLE_OUTPUT ______________________________________________________________ Plan hash value: 181095534 ----------------------------------------------------------- | Id | Operation | Name | ----------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | BITMAP CONVERSION COUNT | | | 3 | BITMAP INDEX FAST FULL SCAN| FLIGHTS_STATUS_BX | ----------------------------------------------------------- 10 rows selected.
The count of cancelled flights is answered by BITMAP CONVERSION COUNT over a scan of the bitmap index, without touching the table. The example drops the index afterward.
How Small It Is
Example:
select index_name, index_type, distinct_keys, leaf_blocks from user_indexes where index_name = 'TICKETS_CABIN_BX';
Output:
INDEX_NAME INDEX_TYPE DISTINCT_KEYS LEAF_BLOCKS ___________________ _____________ ________________ ______________ TICKETS_CABIN_BX BITMAP 2 1
A bitmap index on the cabin of 1,069 tickets has two distinct keys and fits in one leaf block. The index was created and dropped by the example.
When to Use a Bitmap Index
| Good fit | Poor fit |
|---|---|
| Low-cardinality columns: status, type, flag | Unique or near-unique columns |
| Data warehouses and read-mostly tables | Tables with many concurrent inserts and updates |
| Queries combining several conditions with AND and OR | Single-row lookups by key |
A change to a row locks a range of rows covered by the same bitmap piece, so concurrent transactions block each other. That is why bitmap indexes belong in reporting and warehouse schemas rather than in busy transactional tables.
Related Guides
Conclusion
Bitmap indexes store one bitmap per distinct value, which makes them tiny for low-cardinality columns and fast for counts and combined conditions. Use them on read-mostly data, and B-tree indexes where many sessions change rows concurrently.
