Oracle Bitmap Index FAQs with examples

1. What is a Bitmap Index in Oracle?

A Bitmap Index is an index that stores a bitmap, or a series of 0s and 1s, for each distinct column value. It is best suited for columns with low cardinality (few distinct values).

Example:

CREATE BITMAP INDEX idx_gender
ON employees(gender);

2. Why do we use a Bitmap Index?

Bitmap indexes are used to:

• Improve query performance on low-cardinality columns
• Speed up complex queries with multiple conditions
• Reduce storage space compared to some B-Tree indexes
• Improve performance in data warehouses

3. What is Low Cardinality?

Low cardinality means a column has few distinct values.

Examples:

• Gender → Male, Female
• Status → Active, Inactive
• Marital Status → Married, Single
• Yes/No columns

These columns are ideal for bitmap indexes.

4. How do you create a Bitmap Index?

Syntax:

CREATE BITMAP INDEX index_name
ON table_name(column_name);

Example:

CREATE BITMAP INDEX idx_status
ON employees(status);

5. When should you use a Bitmap Index?

Use a bitmap index when:

• The table is mostly read-only
• Columns have few distinct values
• Queries use multiple filtering conditions
• The database is used for reporting or analytics

Example:

SELECT *
FROM employees
WHERE gender = 'Female'
AND status = 'Active';

6. When should you avoid a Bitmap Index?

Avoid bitmap indexes on tables that have:

• Frequent INSERT operations
• Frequent UPDATE operations
• Frequent DELETE operations
• High-concurrency OLTP systems

Bitmap indexes are mainly designed for data warehouse environments.

7. Difference between a Bitmap Index and a B-Tree Index

Bitmap Index	B-Tree Index
Best for low-cardinality columns	Best for high-cardinality columns
Used mainly in data warehouses	Used mainly in OLTP systems
Better for read-heavy workloads	Better for frequent DML operations
Not suitable for frequent updates	Suitable for frequent updates

8. Can a Bitmap Index be created on multiple columns?

Yes.

Example:

CREATE BITMAP INDEX idx_gender_status
ON employees(gender, status);

This improves queries filtering by both gender and status.

9. Can Oracle use multiple Bitmap Indexes?

Yes.

Oracle can combine multiple bitmap indexes efficiently.

Example:

CREATE BITMAP INDEX idx_gender
ON employees(gender);

CREATE BITMAP INDEX idx_status
ON employees(status);

Query:

SELECT *
FROM employees
WHERE gender = 'Male'
AND status = 'Active';

Oracle can combine both bitmap indexes to retrieve the matching rows efficiently.

10. How do you view Bitmap Indexes?

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

11. How do you drop a Bitmap Index?

DROP INDEX idx_gender;

12. What are the advantages of Bitmap Indexes?

• Fast query performance
• Excellent for reporting queries
• Efficient for multiple search conditions
• Require relatively little storage for low-cardinality data
• Oracle can combine multiple bitmap indexes efficiently

13. What are the disadvantages of Bitmap Indexes?

• Poor performance for frequent INSERT, UPDATE, and DELETE operations
• Can cause locking issues in high-concurrency environments
• Not suitable for OLTP applications
• Best suited for read-mostly tables

14. Interview Example

Create a table:

CREATE TABLE employees (
  employee_id NUMBER,
  gender      VARCHAR2(10),
  status      VARCHAR2(20)
);

Create bitmap indexes:

CREATE BITMAP INDEX idx_gender
ON employees(gender);

CREATE BITMAP INDEX idx_status
ON employees(status);

Run the query:

SELECT *
FROM employees
WHERE gender = 'Female'
AND status = 'Active';

Oracle can combine both bitmap indexes to return the matching rows quickly.

15. Real-World Example

Suppose a company stores millions of employee records for reporting.

Common query:

SELECT *
FROM employees
WHERE gender = 'Female'
AND marital_status = 'Married'
AND status = 'Active';

Create bitmap indexes:

CREATE BITMAP INDEX idx_gender
ON employees(gender);

CREATE BITMAP INDEX idx_marital
ON employees(marital_status);

CREATE BITMAP INDEX idx_status
ON employees(status);

Oracle can efficiently combine these bitmap indexes, making reporting queries much faster.

Key Point:
Bitmap indexes are excellent for low-cardinality, read-heavy data warehouse workloads, but they are not a good fit for frequent DML or high-concurrency OLTP systems.