Oracle Bitmap Index vs B-tree Index: When to Use Each

B-tree is Oracle's default, all-purpose index. Bitmap is a specialised structure for low-cardinality columns on read-heavy tables — and a real risk on tables with frequent writes. Here's the difference, and the locking gotcha that decides which one you actually want.

Two different structures for two different jobs

Oracle supports two fundamentally different index structures: B-tree (the default, used almost everywhere) and bitmap (a specialised structure for a specific kind of column and workload). Picking the wrong one doesn't just cost performance — for bitmap indexes on a heavily-written table, it can cause serious locking problems.

B-tree vs bitmap at a glance

	B-tree	Bitmap
Best for	high-cardinality columns (many distinct values)	low-cardinality columns (few distinct values — status flags, gender, region)
Storage	one index entry per row, pointing to a ROWID	a compressed bitmap per distinct value, one bit per row
Combining multiple indexes	usually one index per access path	AND/OR bitmaps together efficiently — the basis of star-query optimisation in data warehouses
Concurrent DML (INSERT/UPDATE/DELETE)	row-level locking — safe for OLTP	a single bitmap segment covers a range of rows — updating one row can block others updating different rows in that same range
Typical use case	OLTP: primary keys, foreign keys, unique lookups	read-heavy analytics/data-warehouse tables with low-cardinality filter columns
Availability	every Oracle edition	typically an Enterprise Edition feature — confirm against your current licence

Creating each

Same syntax family, different keyword.

-- B-tree (default — no keyword needed)
CREATE INDEX idx_emp_email ON employees (email);

-- Bitmap
CREATE BITMAP INDEX idx_emp_status ON employees (employment_status);

The concurrency gotcha

This is the fact that actually matters in practice: a bitmap index entry doesn't map to a single row the way a B-tree entry does — it maps to a range of rows via a compressed bitmap segment. When one session updates a row, Oracle has to lock the entire bitmap segment covering that row's range, not just the row. On a table with frequent concurrent updates, this can turn what looks like unrelated updates into serious lock contention — the reason bitmap indexes are recommended for read-mostly analytical tables, and actively discouraged on busy OLTP tables.

Frequently asked questions

Can I have both a B-tree and a bitmap index on the same table?

Yes — indexes are chosen per column (or column combination) based on that column's own cardinality and access pattern, not a table-wide setting. A table might have B-tree indexes on its primary key and foreign keys, and a bitmap index on a low-cardinality status column, simultaneously.

What counts as "low cardinality" for a bitmap index?

There's no fixed threshold, but the classic candidates are columns with roughly a handful to a few hundred distinct values relative to a large table — gender, order status, region, boolean flags. A column that's nearly unique (like an email address) is a poor bitmap candidate and a good B-tree candidate.

Are bitmap indexes available in Oracle Standard Edition?

Bitmap indexes have historically been an Enterprise Edition feature. Licensing details can change between versions, so confirm current availability against your specific Oracle edition and version before designing around them.

Why did my OLTP table start seeing lock waits after I added a bitmap index?

This is the classic bitmap-index-on-OLTP mistake. A bitmap index entry covers a range of rows in a compressed segment, so concurrent updates to different rows in that same range can block each other. If a table has frequent concurrent writes, a B-tree index (even on a lower-cardinality column) is usually the safer choice.