Essential Principles for Building Effective Oracle Indexes (B‑Tree & Bitmap)

Index Creation Principles

Determine whether the table is accessed mainly by heavy read queries or heavy insert/update/delete operations.

Build indexes to support frequent WHERE‑clause columns; analyze SQL statements and index those fields.

If a query applies a function (e.g., UPPER) to a column, consider a function‑based index.

Index columns frequently used in GROUP BY or ORDER BY.

Create indexes on join columns (primary/foreign keys).

For multi‑column queries, create composite indexes, ordering the columns by usage frequency.

Composite indexes improve performance but consume more space and take longer to rebuild.

Small tables may suffer performance loss from indexing.

Avoid indexing low‑cardinality columns such as gender.

Do not index columns with very few distinct values.

Oracle B‑Tree Index

B‑Tree indexes are the default, general‑purpose indexes in Oracle and can include up to 32 columns. create index indexName on tableName(columnName); Key characteristics:

Indexes do not store NULL values; single‑column indexes omit NULLs, composite indexes omit rows where all indexed columns are NULL. Consequently, predicates like IS NULL cannot use the index.

Not suitable for columns with very few distinct values.

Leading wildcard searches (e.g., LIKE '%XX' or LIKE '%XX%') cannot use the index, causing full‑table scans.

Oracle Bitmap Index

Bitmap indexes store a bitmap for each distinct key value and can include up to 30 columns. create bitmap index indexName on tableName(columnName); Key characteristics:

Space‑efficient and fast to create and query; only the bitmap and ROWIDs are stored.

Unsuitable for columns with a large number of distinct values.

Not ideal for columns that are frequently updated (INSERT/UPDATE/DELETE).

Can store NULL values, allowing IS NULL predicates to use the index.

Aggregations such as SELECT COUNT(...) can be answered directly from the bitmap.

Set operations (AND, OR, IN) on indexed columns are performed on the bitmaps, yielding rapid results.

Bitmap Index Limitations

The rule‑based optimizer does not consider bitmap indexes.

Altering a table that contains bitmap‑indexed columns invalidates those indexes.

Bitmap indexes do not enforce any integrity constraints.

They cannot be declared as unique indexes.

When an Index Is Not Used

Optimizer estimates a full table scan is cheaper than using the index (e.g., SELECT * FROM T WHERE Y=XXX).

If the indexed column contains many NULLs, the optimizer may skip the index; using WHERE column IS NOT NULL can help.

Applying functions to indexed columns prevents index usage; create a function‑based index to avoid this.

Implicit data‑type conversion (e.g., comparing a VARCHAR2 column to a numeric literal) forces the optimizer to apply a conversion function, bypassing the index.

For very small tables that fit in a few data blocks, a full scan is faster than using an index.