When a column in an Oracle table stores only two distinct values like 'Yes' or 'No', creating a standard B-tree index on it is not efficient, because:
- The column has low cardinality (few distinct values),
- B-tree indexes are most effective on high-cardinality columns.
✅ Best Practice: Bitmap Index
You should create a bitmap index on that column.
📌 Why Bitmap Index?
- Ideal for columns with low cardinality (few unique values),
- Efficient for queries like:
SELECT * FROM table_name WHERE status = 'Yes';- Performs well in combination with other bitmap indexes (for multi-column filtering),
- Uses less space than B-tree for such data.
⚠️ Considerations:
- Bitmap indexes are not recommended on columns that are frequently updated in OLTP systems, because:
- They can cause locking issues,
- Best suited for data warehouses or read-heavy workloads.
✅ Syntax:
CREATE BITMAP INDEX idx_status_bm ON your_table(status_column);
If you're on a transactional (OLTP) system and the column is updated very frequently, then:
- Consider not indexing it, or
- Use a function-based index only if queries filter with additional conditions and performance is critical.
Tags:
Oracle