Bitmap Indexes for Analytical Workloads, DML Costs, and Concurrency Limitations

Learning outcomes

An analyst filters ServiceHub’s 50-million-row history by region, severity, contract tier, and completion flag. Each column has few distinct values, and ad hoc combinations change constantly. Bitmap indexes can make those boolean combinations efficient. The same design copied onto the live work-order OLTP table, however, causes unrelated updates to wait on one another. The missing design variable is not cardinality—it is write concurrency.

1. Bitmap indexes encode membership, then combine membership efficiently

A bitmap index associates each key value with bitmaps representing row membership. Oracle can combine bitmaps with efficient AND/OR operations before converting qualifying positions to rowids. That is attractive for warehouse-style predicates such as region='NORTH' AND severity='HIGH' AND completed='Y', especially when many low-to-moderate-cardinality dimensions are combined ad hoc.

Oracle’s physical storage still uses index structures to organize bitmap pieces, but the logical access model differs from a B-tree that stores one rowid entry per key occurrence.

CREATE BITMAP INDEX sh08_bm_region_ixON servicehub_ix_fact(region_code);CREATE BITMAP INDEX sh08_bm_severity_ixON servicehub_ix_fact(severity_code);CREATE BITMAP INDEX sh08_bm_completed_ixON servicehub_ix_fact(completed_flag);BEGIN    DBMS_STATS.GATHER_TABLE_STATS(USER, 'SERVICEHUB_IX_FACT', cascade => TRUE);END;/SELECT index_name, index_type, distinct_keys, num_rowsFROM user_indexesWHERE table_name = 'SERVICEHUB_IX_FACT'ORDER BY index_name;

2. Low cardinality is a clue, not the adoption criterion

Current Oracle guidance explicitly positions bitmap indexes for data-warehouse workloads with low DML activity. Their advantage comes from compressed membership representation and cheap boolean combinations. The cost is DML lock granularity: when one indexed row changes, Oracle locks the bitmap index key entry, which can cover many rows represented by that key.

Therefore, a two-value completed_flag on a hot OLTP table is often a worse bitmap candidate than a 20-value dimension on a mostly read-only fact table. Workload classification outranks a simple distinct-value count.

3. Observe bitmap combination as a possible optimizer strategy

EXPLAIN PLAN SET STATEMENT_ID = 'SH08L3_BITMAP'FORSELECT COUNT(*)FROM servicehub_ix_factWHERE region_code = 'NORTH'  AND severity_code = 'HIGH'  AND completed_flag = 'Y';SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH08L3_BITMAP', 'BASIC +PREDICATE'));

Depending on data size and statistics, you may see bitmap operations such as BITMAP INDEX SINGLE VALUE, BITMAP AND, and bitmap-to-rowid conversion—or Oracle may choose a full scan on the tiny lab table. The plan is evidence of a cost decision, not a syntax promise.

4. Deliberately wrong approach: put a bitmap index on a hot status column

Imagine two operators updating two different work orders that currently share the OPEN bitmap key. With ordinary B-tree indexing, different row updates can often proceed independently. With a bitmap index, the indexed key entry covers many rows; changing one row’s membership can cause the second session to wait even though it targets a different table row.

This is neither a deadlock nor “Oracle locking the whole table.” It is contention on bitmap index key structures created by an OLTP-inappropriate access method.

5. Two-session lab: make bitmap DML contention visible

Use two SQLcl/SQL*Plus sessions connected as the same disposable owner. Do not run this against a shared production schema.

DROP TABLE servicehub_ix_hot IF EXISTS PURGE;CREATE TABLE servicehub_ix_hot (    work_order_id NUMBER PRIMARY KEY,    status_code   VARCHAR2(12) NOT NULL,    note_text     VARCHAR2(80));INSERT INTO servicehub_ix_hotSELECT LEVEL, 'OPEN', 'seed-' || TO_CHAR(LEVEL)FROM dualCONNECT BY LEVEL <= 200;CREATE BITMAP INDEX sh08_hot_status_bmON servicehub_ix_hot(status_code);COMMIT;

UPDATE servicehub_ix_hotSET status_code = 'CLOSED'WHERE work_order_id = 1;-- Do not COMMIT yet.

UPDATE servicehub_ix_hotSET status_code = 'HOLD'WHERE work_order_id = 2;-- This can wait because both changes modify bitmap-key membership.-- From an observer session with catalog privileges, inspect the wait:SELECT sid, serial#, event, blocking_session, row_wait_obj#FROM v$sessionWHERE username = USERORDER BY sid;

The exact wait event and timing can vary, but the important evidence is that Session B can be blocked despite targeting a different table row. End Session A with ROLLBACK; Session B can then complete or be rolled back. Always clean up both transactions.

-- Session AROLLBACK;-- Session B, after it resumesROLLBACK;-- One session after both are cleanDROP TABLE servicehub_ix_hot PURGE;

6. Compare the mechanism with a B-tree

For a hot OLTP status column, a B-tree may still be unnecessary if the predicate is unselective. If another access pattern justifies an index, a B-tree maintains rowid-oriented entries and normally avoids the bitmap key’s multirow lock scope. The correct repair is workload redesign—not blindly replacing every bitmap index with a B-tree.

Warehouse ETL can also make bitmap indexes inconvenient during bulk loads. A common operational pattern is to load data in a controlled window and build/rebuild appropriate indexes as part of the warehouse process, but the exact strategy depends on partitioning, load method, recovery requirements, and licensing.

7. Hands-on analytical lab

DROP TABLE servicehub_ix_fact IF EXISTS PURGE;CREATE TABLE servicehub_ix_fact (    event_id        NUMBER PRIMARY KEY,    region_code     VARCHAR2(10) NOT NULL,    severity_code   VARCHAR2(10) NOT NULL,    completed_flag  CHAR(1) NOT NULL,    amount           NUMBER(10,2) NOT NULL);INSERT INTO servicehub_ix_factSELECT    LEVEL,    CASE MOD(LEVEL,4)      WHEN 0 THEN 'NORTH'      WHEN 1 THEN 'SOUTH'      WHEN 2 THEN 'EAST'      ELSE 'WEST'    END,    CASE MOD(LEVEL,3)      WHEN 0 THEN 'HIGH'      WHEN 1 THEN 'MEDIUM'      ELSE 'LOW'    END,    CASE MOD(LEVEL,2) WHEN 0 THEN 'Y' ELSE 'N' END,    MOD(LEVEL * 37, 10000) / 10FROM dualCONNECT BY LEVEL <= 10000;CREATE BITMAP INDEX sh08_bm_region_ix ON servicehub_ix_fact(region_code);CREATE BITMAP INDEX sh08_bm_severity_ix ON servicehub_ix_fact(severity_code);CREATE BITMAP INDEX sh08_bm_completed_ix ON servicehub_ix_fact(completed_flag);BEGIN    DBMS_STATS.GATHER_TABLE_STATS(USER, 'SERVICEHUB_IX_FACT', cascade => TRUE);END;/SELECT COUNT(*) AS matching_rowsFROM servicehub_ix_factWHERE region_code = 'NORTH'  AND severity_code = 'HIGH'  AND completed_flag = 'Y';

DROP TABLE servicehub_ix_fact PURGE;

8. Production judgment

Choose bitmap indexes for read-mostly analytical workloads whose predicates benefit from bitmap combination. Reject the design for high-concurrency OLTP simply because the column has few values. Monitor DML waits, load windows, segment size, statistics, and query plans. If the table’s write profile changes, revisit the index architecture.

No extra pack or COMPATIBLE change is required for the Free lab. Bitmap indexes are included in Free in the current matrix, but other offerings differ. Lesson 4 moves to another physical design choice: an index-organized table makes the primary-key B-tree itself the table’s row storage.

Authoritative references

• Database Concepts — Bitmap Indexes — bitmap representation and DML locking implications
• Designing and Developing for Performance — data-warehouse suitability and boolean combination
• Optimizer Access Paths — bitmap access operations
• CREATE INDEX — bitmap index DDL
• Licensing Information — offering-specific bitmap-index entitlement