Bitmap Index Use Case

Oracle bitmap index deep dive

Bitmap IndexesWhen they are brilliant, when they are dangerous, and how to use them correctly in Oracle

Bitmap indexes are one of the most misunderstood indexing features in Oracle. People hear “good for low-cardinality columns” and stop there. In reality, bitmap indexes are about more than cardinality: they are about workload shape, concurrency, join patterns, query combinations, storage behavior, partitioning rules, and the optimizer’s ability to combine bitmaps efficiently before converting them back to rowids.

Contents

01 What bitmap indexes are02 Why they work well03 When not to use them04 Reading plans and bitmap operations05 Single-table bitmap indexes06 Bitmap join indexes07 Partitioning and restrictions08 Practical design rules09 End-to-end demo10 Knowledge check

Section 01

What a bitmap index actually is

A B-tree index stores keys with row location references one entry at a time. A bitmap index stores the row locations for each distinct key value as a bitmap. Conceptually, each distinct value owns a string of bits; a bit is set when the corresponding row contains that value. Oracle documents this internal model directly in the SQL language reference and concepts materials. A bitmap index is therefore not just “another index type” - it is a different access representation optimized for combining many predicates efficiently in read-heavy workloads.

Key idea

Bitmap indexes are strongest when the database can answer a query by combining several low-cardinality predicates with bitmap operations such as BITMAP AND, BITMAP OR, and BITMAP MERGE before touching the table rows.

Recollect: “Low cardinality” is the starting clue, not the full rule. The real win comes from combining multiple predicates cheaply in analytic workloads.

Section 02

Why bitmap indexes work so well in reporting and warehousing

Oracle’s documentation repeatedly positions bitmap indexes as a data warehousing feature because they are suited to low-concurrency, read-heavy environments. Their biggest advantage is that the optimizer can combine multiple bitmap indexes very efficiently, which is especially useful when users ask ad hoc questions with several equality predicates. Oracle also documents execution plan operators such as BITMAP AND, BITMAP OR, BITMAP MERGE, and BITMAP CONVERSION TO ROWIDS that show this process directly in plans.

Ad hoc filtering

Analysts often combine many low-cardinality filters such as region, channel, promotion, or status in different ways from one query to the next.

Star queries

Bitmap indexes are a natural fit in star-schema workloads, where many dimension attributes drive fact-table filtering.

Compact storage

Because values are encoded as bitmaps, bitmap indexes can be extremely space-efficient in the right data distribution.

Optimizer angle

The optimizer does not have to pick one bitmap index and ignore the others. It can combine several, which is exactly why bitmap indexes age well in analytic SQL with many optional filters.

Section 03

When bitmap indexes are the wrong choice

This is where many teams get into trouble. Oracle states clearly that bitmap indexes are not appropriate for applications with many concurrent transactions. The reason is locking behavior: a bitmap key can map to many rows, so updating a single row can lock a much larger bitmap entry range than you would see with a B-tree index. In heavy OLTP systems that means contention risk, not just slower maintenance.

Bad fit

• High-concurrency OLTP tables
• Frequent singleton INSERT/UPDATE/DELETE activity
• Columns whose usefulness is mainly range scans
• Workloads where locking side effects matter more than scan speed

Better fit

• Read-mostly reporting tables
• Fact tables in star schemas
• Batch-loaded or nightly-refreshed warehouse data
• Queries combining many equality predicates

Do not simplify too much

It is true that low cardinality often points toward bitmap indexes, but Oracle’s own materials also show that concurrency and DML behavior are just as important. A technically low-cardinality column on a hot OLTP table is still a poor bitmap candidate.

Recollect: The sentence “bitmap indexes are for low-cardinality columns” is incomplete. The better sentence is “bitmap indexes are for low-cardinality columns in the right workload.”

Section 04

How to read execution plans with bitmap operations

Once you know the plan operators, bitmap-index plans become much easier to reason about. Oracle’s plan language usually shows a sequence like bitmap single-value access, bitmap combination, and then a conversion to rowids before table access. If the query includes grouping or star transformations, the plan can become more complex, but these operators remain the main clues.

Plan operator	Meaning	Why you care
BITMAP INDEX SINGLE VALUE	Fetches a bitmap for one equality predicate	Shows Oracle is using the bitmap index directly for a specific value
BITMAP AND	Intersects multiple bitmap vectors	Classic sign that several low-cardinality filters are being combined efficiently
BITMAP OR	Unions multiple bitmap vectors	Useful when queries have alternative filter branches
BITMAP MERGE	Merges bitmaps, often from range or more complex conditions	Helps explain more advanced bitmap processing
BITMAP CONVERSION TO ROWIDS	Transforms the final bitmap into rowids	This is the bridge from bitmap filtering to actual row access

SQL - Explain a bitmap-friendly query

EXPLAIN PLAN FOR
SELECT product_category, SUM(sale_amount)
FROM   sales_fact
WHERE  region = 'WEST'
  AND  order_status = 'COMPLETE'
  AND  channel = 'ONLINE'
GROUP BY product_category;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

A representative bitmap plan usually includes one single-value bitmap access per indexed predicate, then BITMAP AND, then conversion to rowids. The exact cost, cardinality, and row counts vary by data and statistics, so the safe way to teach this is to focus on the operator pattern rather than inventing fixed numbers.

Section 05

Designing single-table bitmap indexes well

The most common starting point is a single-table bitmap index on a fact or reporting table. Oracle’s SQL reference supports syntax such as CREATE BITMAP INDEX ... ON table(column), including local partitioned variants. For learners, the important design question is not simply “Is the column low cardinality?” but “Will users combine this column with other low-cardinality predicates often enough to justify the index?”

Strong candidates

Region, channel, gender, status, flag columns, yes/no attributes, small category sets.

Borderline candidates

Columns with moderate distinct counts that still participate in multi-filter warehouse queries; these must be tested rather than assumed.

Weak candidates

High-cardinality identifiers, transaction IDs, rapidly changing values, and columns primarily used for range predicates.

SQL - Create bitmap indexes on fact attributes

CREATE BITMAP INDEX bix_sales_region
  ON sales_fact(region);

CREATE BITMAP INDEX bix_sales_status
  ON sales_fact(order_status);

CREATE BITMAP INDEX bix_sales_channel
  ON sales_fact(channel);

SQL - Inspect column distinctness

SELECT owner,
       table_name,
       column_name,
       num_distinct,
       density,
       histogram
FROM   dba_tab_col_statistics
WHERE  owner = 'SH'
  AND  table_name = 'SALES'
ORDER BY num_distinct;

Design habit

Use statistics as a clue, not a rigid threshold. Oracle’s own warehousing guidance emphasizes workload context more than a magical numeric cutoff.

Section 06

Bitmap join indexes: one of the most powerful warehouse features

Bitmap join indexes extend the idea further: Oracle can index a fact table using values from joined dimension tables. This is particularly useful in star schemas because it can remove the need to visit some dimension tables during execution. Oracle’s SQL reference documents bitmap join index syntax with joins, and its warehousing guidance positions them as a performance feature for star-query environments.

SQL - Create a bitmap join index

CREATE BITMAP INDEX bix_sales_cust_country
ON sales(c.country_id)
FROM sales s, customers c
WHERE s.cust_id = c.cust_id;

The key idea is subtle but important: the index belongs to the fact table, but its indexed attribute comes from the dimension table. That is why bitmap join indexes are a warehouse optimization rather than a generic OLTP tactic.

Join-index caution

Bitmap join indexes are powerful, but they add maintenance complexity. They are best used where data refresh is controlled and query benefit is clear. They are not a casual default.

Section 07

Partitioning, restrictions, and the rules that bite later

Oracle supports bitmap indexes on partitioned tables, including local bitmap indexes, but there are important restrictions around global partitioned bitmap indexes and around temporary tables. The SQL reference explicitly documents several of these constraints. If you work in warehouses, this matters because bitmap indexes and partitioning often appear together in the same design conversation.

Topic	Practical takeaway	Why it matters
Local bitmap indexes	Common and natural on partitioned fact tables	Partition pruning plus bitmap combination can work very well together
Global partitioned bitmap indexes	Restricted by Oracle rules	You cannot assume every partitioning pattern is valid for bitmap indexes
Temporary tables	Bitmap indexes are not generally the right tool here	Warehouse-style read patterns, not transient OLTP staging, are the primary target
Heavy DML	Still a red flag even if the table is partitioned	Partitioning does not magically erase bitmap locking behavior

SQL - Local bitmap index on a partitioned fact table

CREATE BITMAP INDEX bix_sales_channel_local
  ON sales_fact(channel)
  LOCAL;

Recollect: Bitmap-index design is never just about one column. In real systems you must think about partitioning strategy, refresh pattern, and maintenance operations together.

Section 08

Practical design rules that hold up in real systems

There is no single cardinality percentage that decides everything, and Oracle’s own materials stop short of giving a magical threshold. That is good engineering discipline. Instead, use a set of practical questions.

Question 1

Is the workload mostly read-heavy and analytic rather than highly concurrent OLTP?

Question 2

Will users combine this column with other low-cardinality predicates often?

Question 3

Would locking side effects from updates be acceptable on this table?

Question 4

Can the benefit be seen in execution plans and measured in actual reports?

Rule of thumb that ages well

If the table is warehouse-style, the predicates are mostly equality filters, the columns are low cardinality, and users combine several of them in ad hoc queries, bitmap indexes deserve strong consideration. If the table is OLTP-hot, they usually do not.

Section 09

End-to-end demo: adding bitmap indexes to a reporting fact table

This demo stays close to what Oracle actually supports and to how DBAs really test bitmap indexes. Imagine a reporting schema with a fact table SALES_FACT loaded nightly. Users filter frequently by REGION, ORDER_STATUS, and CHANNEL. The goal is to check distinctness, create bitmap indexes on those attributes, validate the execution plan pattern, and compare the plan shape before and after.

Demo principle

This demo uses real Oracle syntax and real verification steps. It avoids invented elapsed times or fake cardinality outputs because those depend on data size, distribution, statistics, and hardware.

Step 1: inspect column statistics and gather stats if needed

SQL - Check distinctness and stats

SELECT column_name,
       num_distinct,
       histogram,
       density
FROM   user_tab_col_statistics
WHERE  table_name = 'SALES_FACT'
  AND  column_name IN ('REGION', 'ORDER_STATUS', 'CHANNEL')
ORDER BY column_name;

-- If stats are stale, gather them before testing
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'SALES_FACT',
    cascade          => TRUE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO');
END;
/

The success condition here is not a specific number; it is that the candidate columns have relatively small NUM_DISTINCT values compared with the table size and that statistics are current enough for a fair plan test.

Step 2: capture the pre-index plan

SQL - Baseline plan before bitmap indexes

EXPLAIN PLAN FOR
SELECT channel, SUM(sale_amount)
FROM   sales_fact
WHERE  region = 'WEST'
  AND  order_status = 'COMPLETE'
  AND  channel = 'ONLINE'
GROUP BY channel;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

Before the bitmap indexes exist, the plan may show a full table scan or another access path. Record the operator pattern so you can compare it after the index creation.

Step 3: create the bitmap indexes

SQL - Create bitmap indexes

CREATE BITMAP INDEX bix_sales_fact_region
  ON sales_fact(region);

CREATE BITMAP INDEX bix_sales_fact_status
  ON sales_fact(order_status);

CREATE BITMAP INDEX bix_sales_fact_channel
  ON sales_fact(channel);

Step 4: verify the indexes exist and are bitmap indexes

SQL - Verify index metadata

SELECT index_name,
       index_type,
       table_name,
       status,
       visibility
FROM   user_indexes
WHERE  table_name = 'SALES_FACT'
  AND  index_name IN (
         'BIX_SALES_FACT_REGION',
         'BIX_SALES_FACT_STATUS',
         'BIX_SALES_FACT_CHANNEL')
ORDER BY index_name;

The success condition is straightforward: INDEX_TYPE should show BITMAP, and the indexes should be valid and usable.

Step 5: re-run the plan and look for bitmap operators

SQL - Validate the new operator pattern

EXPLAIN PLAN FOR
SELECT channel, SUM(sale_amount)
FROM   sales_fact
WHERE  region = 'WEST'
  AND  order_status = 'COMPLETE'
  AND  channel = 'ONLINE'
GROUP BY channel;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY);

What you are looking for here is not an invented cost value. You are looking for plan operators such as BITMAP INDEX SINGLE VALUE, BITMAP AND, and BITMAP CONVERSION TO ROWIDS. If those appear, Oracle is combining the bitmap indexes the way you intended.

Step 6: compare real execution with statistics

SQL - Get actual execution stats

SELECT /*+ gather_plan_statistics */
       channel,
       SUM(sale_amount)
FROM   sales_fact
WHERE  region = 'WEST'
  AND  order_status = 'COMPLETE'
  AND  channel = 'ONLINE'
GROUP BY channel;

SELECT *
FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

This is the right place to validate whether the operator path is actually being used at runtime and whether row counts make sense. Again, the exact row and timing values depend on your data, so do not teach fake outputs here.

Step 7: prove why bitmap indexes are not for hot OLTP tables

The safest teaching approach is not to stage artificial lock contention numbers but to explain the rule clearly: if this same table were updated heavily by many concurrent sessions, the bitmap indexes could create contention that would be unacceptable in OLTP. Oracle’s documentation makes that design warning explicit.

What counts as a successful demo?

A successful demo means the metadata verifies that the indexes are bitmap indexes, the plan shape changes in the expected bitmap-oriented way, and the test is done on a reporting-style table where bitmap locking behavior is not a hidden time bomb.

Section 10

Knowledge check

These questions aim to sharpen decision-making, not just vocabulary. Read the explanations after you submit.

Q1. What is the strongest reason bitmap indexes are useful in reporting workloads?

They always make every query faster than a B-tree.

Oracle can combine several low-cardinality predicates with efficient bitmap operations before accessing rows.

They are required for all partitioned tables.

They remove the need for optimizer statistics.

Correct answer: Bitmap combination is the real superpower. The feature is most valuable when several low-cardinality predicates are combined efficiently in analytic SQL.

Q2. Why are bitmap indexes usually a poor fit for busy OLTP tables?

Because Oracle cannot create them on transactional tables.

Because they cannot be used in SQL execution plans.

Because update activity can create locking and maintenance behavior that is unacceptable under high concurrency.

Because they only work on numeric columns.

Correct answer: Concurrency is the problem. Bitmap indexes are not rejected because they are low-cardinality; they are rejected because their locking behavior is a poor fit for highly concurrent row-by-row OLTP changes.

Q3. Which plan operator is the clearest sign that Oracle is intersecting multiple bitmap predicates?

BITMAP AND

NESTED LOOPS

WINDOW SORT

HASH UNIQUE

Correct answer: BITMAP AND. That operator is the classic signature that several bitmap filters are being combined before row access.

Q4. What is the best summary of a bitmap join index?

A B-tree index that contains only joined rowids.

An index that always replaces fact tables.

An index that exists only in memory.

A bitmap index stored on a fact table but built using values from joined dimension tables.

Correct answer: Fact storage, dimension-driven values. That is exactly why bitmap join indexes are so closely associated with star-schema warehouses.

Q5. In the end-to-end demo, what is the safest way to verify the bitmap-index design actually worked?

Assume it worked because the CREATE INDEX commands succeeded.

Check metadata in USER_INDEXES and inspect the plan for bitmap operators before and after.

Restart the instance and wait for Smart Scan messages.

Drop all B-tree indexes automatically.

Correct answer: Trust, then verify. Metadata tells you the index type; the execution plan tells you whether the optimizer is actually using bitmap operations as intended.

Q6. Which statement about “low cardinality” is the most accurate?

Low cardinality alone is enough to guarantee a bitmap index is the right answer.

Only columns with fewer than exactly 100 distinct values qualify.

Low cardinality is an important clue, but workload pattern and concurrency still decide whether bitmap indexes are appropriate.

Low cardinality only matters for temporary tables.

Correct answer: Context decides. Cardinality is necessary to think about, but it is not enough by itself. Workload and concurrency are just as important.

Q7. What is the healthiest rule of thumb for bitmap-index design?

Use them when the table is read-heavy, predicates are mostly equality filters, columns are low cardinality, and combined filtering is common.

Use them on every column in a fact table.

Never use them with partitioned tables.

Always replace all B-tree indexes with bitmap join indexes.

Correct answer: Match the technology to the workload. That rule is broad enough to be useful and specific enough to prevent common bitmap-index mistakes.