Oracle Bitmap Index Improvements

Bitmap indexes are designed for analytics-style workloads where many rows share the same value (low-cardinality columns such as status flags, region codes, or category identifiers). Instead of storing a long list of row pointers per value, a bitmap index represents each distinct value with a compact bitmap and then combines bitmaps efficiently for multi-predicate queries (AND/OR/NOT).

Internally, bitmap index entries must map bits back to table row locations. Oracle’s bitmap mapping efficiency depends on an estimate of how many rows can exist in a table block. If Oracle must assume a very large “maximum rows per block,” the bitmap representation requires more bits to cover the potential rowid range—making the bitmap index larger than it needs to be.
Oracle provides a table-level optimization that “trains” the database using the table’s current physical reality: Oracle scans the table, determines the maximum number of rows that actually exist in any block, and then restricts the table to that maximum for purposes of bitmap mapping. The practical result is that bitmap indexes created afterward can use fewer bits per bitmap entry and therefore consume less space (and can be faster to scan and combine).

Use the records_per_block_clause to enable or disable this optimization. The clause has two modes:

• MINIMIZE RECORDS_PER_BLOCK — enables the optimization and recalculates the mapping using the table’s current maximum rows-per-block.
• NOMINIMIZE RECORDS_PER_BLOCK — disables the restriction (returns to default behavior).

-- Enable bitmap mapping optimization (recommended before creating bitmap indexes)
ALTER TABLE table_name MINIMIZE RECORDS_PER_BLOCK;

-- Disable bitmap mapping optimization (revert to default behavior)
ALTER TABLE table_name NOMINIMIZE RECORDS_PER_BLOCK;

Because bitmap indexes are built using the mapping assumptions in effect at creation time, treat this as a “set it first, then build indexes” feature.

1. Confirm the table is not empty. Oracle needs real blocks/rows to measure a meaningful maximum rows-per-block.
2. Drop existing bitmap indexes on the table (if any). This table operation is not permitted while bitmap indexes exist.
3. Run ALTER TABLE … MINIMIZE RECORDS_PER_BLOCK.
4. Recreate bitmap indexes (and bitmap join indexes, if applicable).
5. Gather optimizer statistics on the table and indexes so the optimizer can cost bitmap access paths accurately.

This optimization is most relevant when:

• You rely on bitmap indexes for star-schema or dimensional analytics.
• The table’s blocks contain far fewer rows than the theoretical maximum (for example, due to row length, PCTFREE strategy, row expansion patterns, or load processes).
• You want to reduce bitmap index footprint and improve bitmap scan efficiency without changing application SQL.

Oracle notes that the restriction is beneficial primarily for bitmap indexes—so it is generally not something you enable “by default” on OLTP tables that will never use bitmap indexes.

In Oracle 23ai, bitmap indexes remain a targeted tuning tool: they are powerful for decision-support queries, but they are still a poor fit for high-concurrency OLTP tables with heavy DML. Use them where your workload is read-mostly or batch-updated, and where bitmap index combination can replace expensive joins or large table scans.
If your table design involves “small-at-insert, larger-after-update” rows, combine the bitmap strategy with an appropriate PCTFREE policy to reduce row migration and preserve predictable physical layout.

Click the Exercise link below to practice bitmap-index tuning concepts in a controlled scenario.
Bitmap Index Improvements - Exercise