What is INDEX_MERGE in Oracle?

INDEX_MERGE is an optimizer access path that allows Oracle to:

Combine multiple B-tree indexes on the same table
by intersecting or unioning their ROWID sets.

It works by scanning multiple indexes separately and then merging the returned ROWIDs before accessing the table.

It is not:

• A bitmap index

• A composite index

• A bitmap execution method

• A join operation

It is a single-table access strategy.

When Does Oracle Consider INDEX_MERGE?

Oracle may consider it when:

• Multiple single-column B-tree indexes exist

• The query contains predicates on different indexed columns

• No suitable composite index exists

• The combined selectivity is good

• The estimated cost of merging is lower than a full table scan

Example

Indexes:

Oracle has three main options:

1. Use IDX_COL1 and filter on col2

2. Use IDX_COL2 and filter on col1

3. Use INDEX_MERGE and intersect both

Internal Execution Mechanics

Execution happens in stages:

• Perform INDEX RANGE SCAN on the first index

• Perform INDEX RANGE SCAN on the second index

• Collect ROWIDs from both scans

• Sort ROWIDs

• Apply SORT UNIQUE

• Intersect or union the ROWID sets

• Access the table using the final ROWIDs

Typical plan shape:

Important Notes

• Oracle merges ROWID lists, not data blocks

• Sorting requires memory and possibly TEMP space

• Sorting cost cannot be avoided

Types of INDEX_MERGE

Oracle supports two logical forms:

1. Intersection (AND conditions)

ROWIDs from both indexes are intersected.

2. Union (OR conditions)

ROWIDs are unioned.

Cost Model Behavior

Oracle does not frequently choose INDEX_MERGE automatically because:

• Sorting ROWIDs can be expensive

• Cardinality estimation may be inaccurate

• TEMP usage may increase

• Random I/O can grow significantly

For these reasons, Oracle usually prefers:

• A composite index

• A single index with filter

• Or even a full table scan

Practical Position

I am not advocating always using INDEX_MERGE.

It is useful when:

• A composite index does not exist

• Creating a composite index is not optimal

• Creating a new index is not possible

• The query is not a high-frequency OLTP path

INDEX_MERGE should be considered a situational optimization, not a primary indexing strategy.

Oracle is the only major enterprise RDBMS whose optimizer can intersect multiple B-tree indexes on the same table without using bitmap execution, via INDEX_MERGE.