Oracle Low Cardinality Index Guide - Bitmap vs B-Tree Index Selection

Oracle Low Cardinality Index Guide

Understanding when and how to index columns with low cardinality (few distinct values).

What Is Cardinality?

Cardinality = Number of distinct values in a column

Cardinality Level	Distinct Values	Example Columns
Very Low	2-10	gender, status, yes/no flags
Low	10-100	country, category, department
Medium	100-1000	city, product_type
High	1000+	customer_id, email, SSN

-- Check column cardinality
SELECT
  COLUMN_NAME,
  NUM_DISTINCT,
  NUM_NULLS,
  DENSITY
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'HR' AND TABLE_NAME = 'EMPLOYEES'
ORDER BY NUM_DISTINCT;

Index Options for Low Cardinality

Option 1: Bitmap Index

Best for: Data warehouses, low-DML environments

-- Create bitmap index
CREATE BITMAP INDEX emp_status_bix ON employees(status);

-- Advantages:
-- - Compact storage
-- - Excellent for AND/OR/NOT queries
-- - Fast COUNT operations
-- - Good for multiple low-cardinality predicates

Option 2: B-Tree Index (with considerations)

Best for: OLTP with concurrent DML

-- Create B-tree index
CREATE INDEX emp_status_idx ON employees(status);

-- May not be used by optimizer if selectivity is poor
-- Oracle might choose full table scan instead

Option 3: Composite Index

Combine with high-cardinality column

-- Status + employee_id (more selective together)
CREATE INDEX emp_status_empid_idx ON employees(status, employee_id);

-- Useful for queries like:
-- WHERE status = 'ACTIVE' AND employee_id BETWEEN 1000 AND 2000

Option 4: No Index

When table is small or accessed infrequently

-- Sometimes no index is best
-- Full table scan might be faster for:
-- - Small tables (< 1000 rows)
-- - Queries returning > 10-20% of rows
-- - Columns with very low selectivity

Bitmap Index Details

When to Use Bitmap Indexes

-- Perfect for: Multiple AND/OR conditions
SELECT COUNT(*)
FROM sales
WHERE region = 'WEST'
  AND product_category = 'ELECTRONICS'
  AND sale_type = 'RETAIL';

-- Each bitmap index is combined efficiently
-- Uses bitmap AND/OR operations in memory

When NOT to Use Bitmap Indexes

-- Avoid in OLTP with concurrent DML
-- Bitmap indexes lock entire bitmap segment
-- One UPDATE can block many other sessions

-- Check for bitmap lock contention
SELECT EVENT, TOTAL_WAITS
FROM V$SYSTEM_EVENT
WHERE EVENT LIKE '%bitmap%';

Bitmap Index Internals

-- Check bitmap index details
SELECT
  INDEX_NAME,
  INDEX_TYPE,
  BLEVEL,
  LEAF_BLOCKS,
  DISTINCT_KEYS
FROM DBA_INDEXES
WHERE INDEX_TYPE LIKE 'BITMAP%'
  AND OWNER = 'HR';

B-Tree on Low Cardinality

Why B-Tree May Not Be Used

-- Optimizer considers:
-- 1. Selectivity (distinct values / total rows)
-- 2. Clustering factor
-- 3. Cost of index scan vs full table scan

-- Example: status has 3 values, table has 1M rows
-- Selectivity = 3/1000000 = 0.000003 (very poor)
-- Each index entry points to ~333K rows
-- Full table scan likely faster than index scan

Making B-Tree More Effective

-- Add more selective columns
CREATE INDEX emp_status_dept_idx ON employees(status, department_id, employee_id);

-- Use index compression
CREATE INDEX emp_status_idx ON employees(status) COMPRESS 1;

-- Create partial index (function-based)
CREATE INDEX emp_active_idx ON employees(
  CASE WHEN status = 'ACTIVE' THEN employee_id END
);

Performance Comparison

Test Query Performance

-- Enable timing and execution plan
SET TIMING ON
SET AUTOTRACE ON

-- Test with no index
SELECT /*+ FULL(e) */ COUNT(*) FROM employees e WHERE status = 'ACTIVE';

-- Test with B-tree index
SELECT /*+ INDEX(e emp_status_idx) */ COUNT(*) FROM employees e WHERE status = 'ACTIVE';

-- Test with bitmap index
SELECT /*+ INDEX(e emp_status_bix) */ COUNT(*) FROM employees e WHERE status = 'ACTIVE';

Analyze Index Usage

-- Check if optimizer uses the index
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE status = 'ACTIVE';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Look for:
-- TABLE ACCESS FULL = index not used
-- INDEX RANGE SCAN = B-tree index used
-- BITMAP INDEX SINGLE VALUE = bitmap index used

Decision Matrix

Scenario	Recommendation
Data warehouse, read-heavy	Bitmap index
OLTP, concurrent DML	Composite B-tree or no index
Reports with multiple filters	Bitmap indexes on each column
Very small table (< 1000 rows)	No index
Query returns > 20% of rows	Full table scan
Combination of low + high cardinality	Composite B-tree
Single value queries (flag = ‘Y’)	Consider function-based

Practical Examples

Example 1: Status Flag

-- Table: orders, 10M rows
-- Column: is_shipped (Y/N)

-- Poor choice: B-tree index
CREATE INDEX ord_shipped_idx ON orders(is_shipped);
-- Index scan + table access for 5M rows = slow

-- Better: Bitmap for reporting
CREATE BITMAP INDEX ord_shipped_bix ON orders(is_shipped);
-- Good for: SELECT COUNT(*) WHERE is_shipped = 'N'

-- Alternative: Partial index for specific value
CREATE INDEX ord_not_shipped_idx ON orders(order_id)
WHERE is_shipped = 'N';
-- Only indexes unshipped orders

Example 2: Category Filtering

-- Table: products, 500K rows
-- Column: category (20 distinct values)

-- For data warehouse:
CREATE BITMAP INDEX prod_cat_bix ON products(category);

-- For OLTP with concurrent access:
CREATE INDEX prod_cat_name_idx ON products(category, product_name);
-- Useful for: WHERE category = 'X' ORDER BY product_name

Example 3: Combined Low-Cardinality Columns

-- Multiple low-cardinality columns
-- status (3 values), region (10 values), priority (5 values)

-- Bitmap indexes excel here
CREATE BITMAP INDEX ord_status_bix ON orders(status);
CREATE BITMAP INDEX ord_region_bix ON orders(region);
CREATE BITMAP INDEX ord_priority_bix ON orders(priority);

-- Query combines them efficiently
SELECT * FROM orders
WHERE status = 'OPEN'
  AND region IN ('NORTH', 'SOUTH')
  AND priority = 'HIGH';
-- Uses BITMAP AND/OR operations

Monitoring and Maintenance

-- Check bitmap index size vs B-tree
SELECT
  INDEX_NAME,
  INDEX_TYPE,
  LEAF_BLOCKS,
  BYTES/1024/1024 AS size_mb
FROM DBA_INDEXES i
JOIN DBA_SEGMENTS s ON i.INDEX_NAME = s.SEGMENT_NAME
WHERE i.TABLE_NAME = 'ORDERS'
ORDER BY INDEX_TYPE;

-- Bitmap index fragmentation
-- Rebuild after heavy DML
ALTER INDEX ord_status_bix REBUILD;