Why Bitmap Indexes Can Wreck OLTP Systems

Bitmap indexes might seem like a great idea—they save space and are fast for certain types of queries. But in high-traffic OLTP systems, they can be a nightmare. Why? Because they don’t play well with frequent updates and lots of users at the same time. In fact, they can cause serious blocking and performance problems. Let’s walk through an example to see why.

1. Setting Up the Table and Bitmap Index

Here’s a simple table for orders:

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    status VARCHAR2(10) NOT NULL  -- Low-cardinality: 'OPEN', 'CLOSED'
);

We add a few rows:

INSERT INTO orders VALUES (1, 'OPEN');
INSERT INTO orders VALUES (2, 'OPEN');
INSERT INTO orders VALUES (3, 'CLOSED');
COMMIT;

Now, we create a bitmap index on the status column:

CREATE BITMAP INDEX orders_status_bidx ON orders(status);

This is where the trouble starts…

2. Why Bitmap Indexes Cause Locking Issues

Let’s test what happens when two users (sessions) update data at the same time.

Session 1 runs this:

UPDATE orders SET status = 'CLOSED' WHERE order_id = 1;

This update locks the bitmap area for 'OPEN'.

At the same time, Session 2 tries this:

UPDATE orders SET status = 'CLOSED' WHERE order_id = 2;

But it gets stuck—it has to wait until Session 1 finishes and commits. Why?

Here’s what the execution plan for that update looks like:

EXPLAIN PLAN FOR
UPDATE orders SET status = 'CLOSED' WHERE order_id = 1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

OPERATION         | OPTIONS     | OBJECT_NAME
----------------------------------------------
UPDATE            |             | 
  INDEX           | RANGE SCAN  | ORDERS_STATUS_BIDX
  TABLE ACCESS    | BY ROWID    | ORDERS

That bitmap index is the culprit. It locks the whole group of rows with status='OPEN', not just the one you’re updating. That’s why Session 2 is blocked—it’s trying to update a row in the same group.

3. Fix It: Use a B-tree Index Instead

Let’s drop the bitmap index and switch to a B-tree:

DROP INDEX orders_status_bidx;
CREATE INDEX orders_status_idx ON orders(status);  -- B-tree

4. What’s the Difference?

Now run the same update again and check the execution plan:

EXPLAIN PLAN FOR
UPDATE orders SET status = 'CLOSED' WHERE order_id = 1;

OPERATION         | OPTIONS     | OBJECT_NAME
----------------------------------------------
UPDATE            |             | 
  INDEX           | UNIQUE SCAN | SYS_C0012345  -- Primary key
  TABLE ACCESS    | BY ROWID    | ORDERS

This time, the query uses the primary key index, which locks only the row you’re updating. That means Session 2 won’t be blocked anymore when it tries to update its row.

5. What You Should Do in OLTP Systems

Avoid bitmap indexes in OLTP systems:

• They lock too much data.
• They hurt performance under concurrent writes.
• Use B-tree indexes instead—even for columns with few unique values (low cardinality).

When are bitmap indexes okay?

• In data warehouses or OLAP systems where updates are rare.
• When data changes happen in large batches, not row-by-row.

Check if your system is using bitmap indexes:

SELECT index_name, table_name 
FROM user_indexes 
WHERE index_type = 'BITMAP';

Conclusion

Bitmap indexes may look good on paper, but they don’t belong in OLTP systems. They break a key rule: updates should only lock the row being changed—not an entire group of rows. If you want your system to scale and avoid frustrating lock waits, stick with B-tree indexes for transactional workloads.

Always double-check your index types during schema reviews!