Bitmap Index FAQS

1. What is a Bitmap Index in Oracle?

A Bitmap Index uses bitmaps (0s and 1s) to represent the presence of a column value in rows.
Instead of:

Value → ROWID

It stores:

Value → Bitmap (series of 0s and 1s)

Example:

CREATE BITMAP INDEX emp_gender_bm_idx ON employees(gender);

2. How does it work internally?

Example table:

ROWID	gender
1	M
2	F
3	M
4	F

Bitmap index stores:

M → 1 0 1 0

F → 0 1 0 1

Query:

SELECT * FROM employees WHERE gender = 'M';

Oracle reads bitmap for 'M' and fetches rows where bit = 1.

3. When to use a Bitmap Index?

Best for:

• Low-cardinality columns (few distinct values)
• Data warehouse systems
• Analytical queries
• Read-heavy environments
• Columns used in multiple filtering conditions

Examples: gender, status, region, marital_status

4. When NOT to use Bitmap Index?

Avoid in:

• High-DML OLTP systems
• Tables with frequent INSERT/UPDATE/DELETE
• High-concurrency environments

Reason: Locks large portions of index during updates.

5. Low Cardinality

Column	Distinct Values	Good for Bitmap?
gender	2	Yes
status	3	Yes
emp_id	1M	No

6. Performance improvement

Powerful for multiple conditions:

SELECT * FROM sales

WHERE region = 'WEST'

AND status = 'ACTIVE'

AND gender = 'F';

Oracle performs bitmap AND/OR/XOR operations on bits → very fast.

7. Bitmap Merge Operations

Combines bitmaps using:

• AND
• OR
• XOR

Example: Region = WEST AND Status = ACTIVE
Oracle retrieves bitmaps and performs bitwise AND → fetch matching rows efficiently.

8. Bitmap vs B-Tree

Feature	Bitmap Index	B-Tree Index
Best for	Low cardinality	High cardinality
DML performance	Poor	Good
Concurrency	Low	High
AND/OR queries	Excellent	Moderate
OLTP	Not recommended	Recommended
Data Warehouse	Recommended	Sometimes

9. Why not for OLTP?

• Updates affect many bits → large index locking
• Reduces concurrency, can block other sessions

10. Locking behavior

Bitmap indexes lock entire key range, not single rows → not suitable for high concurrency.

11. Storage

• Very compact for low-cardinality columns
• Smaller than B-Tree
• Compression improves storage efficiency

12. Composite Bitmap Index

CREATE BITMAP INDEX sales_bm_idx ON sales(region, status);

Useful in star-schema designs.

13. Bitmap Join Index

Special bitmap index for data warehouse joins:

CREATE BITMAP INDEX sales_cust_region_bm_idx

ON sales(customers.region)

FROM sales, customers

WHERE sales.customer_id = customers.customer_id;

Improves join performance.

14. DML impact

• INSERT → updates bitmap entries
• UPDATE → may rewrite large portions
• DELETE → clears bits

Heavy DML → fragmentation, locking, performance drop.

15. Advantages

• Excellent for low-cardinality columns
• Efficient multi-column filtering
• Supports complex analytical queries
• Compact storage
• Ideal for data warehouse

16. Disadvantages

• Poor performance with frequent DML
• Lock contention
• Not for OLTP
• Not for high-cardinality columns

17. Real-world example

Query:

SELECT SUM(amount)

FROM sales

WHERE region = 'WEST'

AND status = 'ACTIVE'

AND gender = 'F';

With bitmap indexes on region, status, gender → Oracle performs bitmap AND operations → extremely fast.

18. Performance tuning tips

• Use only in read-heavy systems
• Star-schema dimensions
• Avoid frequently updated tables
• Combine multiple bitmap indexes for analytics
• Monitor locking behavior

19. Common mistakes

• Using in OLTP
• Indexing high-cardinality columns
• Not understanding locking impact
• Mixing heavy DML with bitmap index

20. Interview Tip

“A Bitmap index stores bitmaps representing row existence for each distinct column value. Efficient for low-cardinality columns and complex analytical queries using multiple conditions. Not suitable for high-DML OLTP due to locking and concurrency. Mainly used in data warehouse environments.”

B-Tree Index FAQS

1. What is a B-Tree Index in Oracle?

A B-Tree (Balanced Tree) index is the default index type in Oracle.
It is designed for:

• High-cardinality columns
• Equality searches (=)
• Range searches (>, <, BETWEEN)
• Join conditions

Example:

CREATE INDEX emp_salary_idx ON employees(salary);

If you don’t specify type, Oracle creates a B-Tree index.

2. Why is it called “Balanced Tree”?

• All leaf nodes are at the same depth
• Automatically balances during inserts and deletes
• Search time remains efficient (O(log n))

3. Internal structure of a B-Tree index

Contains:

1.    Root Block – Starting point of search

2.    Branch Blocks – Guide navigation

3.    Leaf Blocks – Store indexed column value + ROWID

Structure:

Root

  ↓

Branch

  ↓

Leaf → (Value + ROWID)

4. How does a B-Tree index search work?

Example:

SELECT * FROM employees WHERE salary = 5000;

Oracle:

1.    Starts at root block

2.    Navigates branch blocks

3.    Finds matching value in leaf

4.    Uses ROWID to fetch row

Fast because it avoids full table scan.

5. When to use a B-Tree index?

Best for:

• High-cardinality columns
• Primary keys and foreign keys
• Frequently filtered columns
• Range conditions

Examples: emp_id, email, transaction_id

6. When NOT to use a B-Tree index?

Avoid for:

• Low-cardinality columns (gender, status)
• Very small tables
• Heavily updated columns
• Rarely filtered columns

For low-cardinality in Data Warehousing → use Bitmap index.

7. High Cardinality

Column	Distinct Values	Good for B-Tree?
emp_id	1 million	Yes
gender	2	No
status	3	No

 

                      
8. Types of B-Tree scans

• Index Unique Scan – searches a unique value, fastest (PK)
• Index Range Scan – WHERE salary BETWEEN 5000 AND 10000
• Index Full Scan – scans entire index, used when index smaller than table
• Fast Full Index Scan – reads index like a table, no table access needed

9. Clustering Factor

Measures how ordered table data is relative to the index.

• Low → fewer block reads, better performance
• High → more block reads, worse performance

10. DML impact

Operation	Impact
INSERT	Adds new leaf entry
UPDATE (indexed column)	Removes + re-inserts entry
DELETE	Marks entry as deleted

Heavy DML → leaf block splits and fragmentation.

11. Leaf Block Split

Occurs when leaf block is full and new value is inserted. Frequent splits reduce performance.
Common in sequential inserts (like sequence PK). Solution: use reverse key index.

12. Reverse Key B-Tree Index

Reverses byte order of indexed column to prevent hot block contention.

CREATE INDEX emp_id_rev_idx ON employees(emp_id) REVERSE;

13. Composite B-Tree Index

Index on multiple columns:

CREATE INDEX emp_dept_salary_idx ON employees(dept_id, salary);

Column order matters. Best if first column is used in WHERE clause.

14. Function-Based B-Tree Index

Index on expression:

CREATE INDEX emp_upper_name_idx ON employees(UPPER(name));

Improves performance for queries like:

WHERE UPPER(name) = 'JOHN'

15. Storage

Depends on number of rows, column size, distinct values. Stored in separate segment, typically smaller than table.

16. Advantages

• Fast lookups
• Efficient range queries
• Automatically balanced
• Good for OLTP
• Default, widely used

17. Disadvantages

• Slows DML
• Not efficient for low-cardinality
• Fragmentation under heavy deletes
• Requires storage

18. Performance tuning tips

• Index frequently filtered columns
• Index foreign keys
• Avoid over-indexing
• Monitor clustering factor
• Rebuild if fragmented
• Keep statistics updated
• Choose correct column order in composite index

19. Real-world example

Bad query:

SELECT * FROM orders WHERE customer_id = 100;

Without index → full table scan.

Better:

CREATE INDEX orders_cust_idx ON orders(customer_id);

Query uses index range scan.

20. Interview Tip

“A B-Tree index is Oracle’s default balanced tree index that stores column values with ROWIDs. Provides fast logarithmic search performance, ideal for high-cardinality columns and range queries. Improves SELECT performance but adds overhead to insert, update, delete operations.”