Oracle Indexing Strategy: Choosing the Right Index (and Knowing When Not To)

Indexes are the first thing people reach for when a query is slow, and the last thing they think about when it is fast. Both instincts cause trouble. An index is not free performance - it is a trade: faster reads for slower writes and more space. Add too few and queries scan millions of rows; add too many and every insert crawls while the optimizer drowns in options. After eighteen years of tuning production databases, my view is that indexing is less about knowing every index type and more about a few good decisions made deliberately. This guide covers the index types that matter, how to choose between them, composite-column order, function-based indexes, and - just as important - when NOT to index.

1. The Trade Every Index Makes

Before any index type, understand the deal you are making. An index is a separate, sorted structure that lets Oracle find rows without scanning the whole table. That speeds up reads. But every INSERT, UPDATE (of indexed columns), and DELETE must also maintain every affected index - so writes get slower and the database uses more space and more redo.

This is why 'add an index' is not automatically good advice. On a read-heavy reporting table, more indexes usually help. On a write-heavy OLTP table with fifteen indexes, each insert updates sixteen structures, and the table can become insert-bound. The right number of indexes is the fewest that serve your actual query patterns - and finding that number is the real skill.

2. B-tree: The Default, and Usually Right

The balanced-tree (B-tree) index is Oracle's default and the correct choice for the large majority of cases. It excels on high-cardinality columns - those with many distinct values, like a primary key, an email address, an order number, a date.

-- The everyday index
CREATE INDEX ord_customer_ix ON orders(customer_id);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX cust_email_uix ON customers(email);

B-tree indexes stay balanced automatically, support range scans (BETWEEN, <, >) and equality alike, and are the backbone of OLTP performance. When in doubt, it is a B-tree. The interesting decisions are about the columns and order, not the type.

3. Bitmap: Powerful in Warehouses, Dangerous in OLTP

A bitmap index stores, for each distinct value, a bitmap of which rows have it. This makes it superb for low-cardinality columns - gender, status, region, yes/no flags - especially when queries combine several such columns with AND/OR, as analytics queries do.

-- Great in a read-mostly data warehouse
CREATE BITMAP INDEX sales_region_bx ON sales(region);
CREATE BITMAP INDEX sales_channel_bx ON sales(channel);
-- WHERE region='EAST' AND channel='ONLINE' -> bitmaps combined very fast

But there is a trap that catches people badly: bitmap indexes and OLTP do not mix. Because a single bitmap segment covers many rows, one row's DML can lock a whole range of rows for other writers. On a table with concurrent inserts and updates, bitmap indexes cause crippling lock contention (the enq: TX waits from the locking guide). Rule of thumb: bitmap indexes belong in read-mostly warehouses and reporting tables, never on hot transactional tables - use B-tree there even for low-cardinality columns.

4. Composite Indexes: Column Order Is Everything

A composite (multi-column) index covers several columns at once. Used well, it can satisfy a query entirely from the index. But the ORDER of the columns changes everything about which queries it helps.

-- Index on (customer_id, order_date)
CREATE INDEX ord_cust_date_ix ON orders(customer_id, order_date);

This index is excellent for WHERE customer_id = :x AND order_date > :d, and usable for WHERE customer_id = :x alone. But it is largely useless for WHERE order_date > :d ALONE - because the leading column (customer_id) is not in the predicate, and an index is a phone book sorted by the first column. You cannot find everyone born in June efficiently in a book sorted by surname.

The design rules: lead with the column(s) used in equality predicates, then range columns; among equality columns, put the most selective first when queries vary. And aim to cover a query's WHERE and SELECT columns so Oracle can answer from the index without touching the table (an 'index-only' scan) - one of the highest-value tuning wins available, and central to the method in the performance tuning guide.

5. Function-Based Indexes: Making Expressions Indexable

A normal index on last_name does nothing for WHERE UPPER(last_name) = 'KHAN' - applying a function to the column hides it from the index, forcing a full scan. A function-based index indexes the EXPRESSION instead:

-- Now case-insensitive searches use an index
CREATE INDEX cust_uname_fbi ON customers(UPPER(last_name));
-- WHERE UPPER(last_name)='KHAN'  -> uses cust_uname_fbi

-- Also great for computed predicates and partial indexing
CREATE INDEX ord_open_fbi ON orders(CASE WHEN status='OPEN' THEN order_id END);
-- indexes only OPEN orders -> small, fast 'partial' index

Function-based indexes quietly solve a huge class of 'why won't it use the index?' problems: date truncation, concatenation, case-insensitive search, and computed flags. The one caution is consistency - the query's expression must match the index's expression exactly.

6. When NOT to Index

Restraint is half of good indexing. Do not add an index when:

• The column is rarely queried. An index used by no query is pure overhead - write cost and space with zero read benefit.
• The table is tiny. Oracle full-scans a small table faster than navigating an index. Indexes matter at scale, not on a 200-row lookup.
• The column is low-cardinality on a hot OLTP table. Neither B-tree (low selectivity) nor bitmap (locking) fits well; often the answer is no index.
• A query returns a large fraction of the table. If a query reads 40% of rows, a full scan beats an index anyway - the optimizer knows this, which is why 'the index exists but isn't used' is often correct behaviour, not a bug.
• You are duplicating coverage. An index on (A) is redundant if you already have (A, B) - the composite already serves leading-column A.

7. Finding and Removing Dead Indexes - Safely

Over years, tables accumulate indexes nobody uses - each one taxing every insert. Finding them safely is a two-step process, and the invisible index is the professional's tool.

-- Which indexes are actually being used? (monitor over a real workload period)
SELECT name AS index_name, total_access_count, last_used
FROM   dba_index_usage
WHERE  owner='SALES' ORDER BY total_access_count;

-- Suspect an index is unused? Make it INVISIBLE (optimizer ignores it,
-- but it's still maintained) - a reversible test:
ALTER INDEX ord_old_ix INVISIBLE;
-- ...run for a week; if nothing breaks and nothing slows...
DROP INDEX ord_old_ix;
-- panic? instantly reverse:  ALTER INDEX ord_old_ix VISIBLE;

Invisible indexes let you 'soft-drop' an index and watch the effect without the risk of a real drop - if a critical query slows, one command brings it back. It is far safer than dropping and hoping, and it is also how you test whether a NEW index helps before committing to its write cost. (Index Usage Tracking via DBA_INDEX_USAGE is the modern, low-overhead way to see real usage.)

8. A Real Case: Fifteen Indexes, Slow Inserts

A client's order-intake table had degraded to the point where inserts took noticeably long under load, threatening the checkout flow. The table carried fifteen indexes - accumulated over years, each added to speed up some report, none ever removed. Every insert was maintaining sixteen structures.

The fix was subtraction, done safely. Index Usage Tracking showed that six of the fifteen had not been touched by any query in a month. We made those six invisible, ran for a week with no query regressions, then dropped them. Insert time fell sharply and the checkout pressure eased. Two of the remaining nine were redundant leading-column duplicates of composite indexes, so those went too. The lesson the team kept was the one worth keeping: an index is a standing cost on every write, so the question is never just 'would an index help this query?' but 'does this index still earn its place?' - and the invisible-index trick lets you answer it without gambling with production.

Frequently Asked Questions

When should I use a B-tree vs a bitmap index in Oracle?

Use B-tree (the default) for high-cardinality columns and any table with concurrent writes - essentially all OLTP. Use bitmap only for low-cardinality columns in read-mostly data warehouses, where they combine efficiently across multiple filters. Never put bitmap indexes on hot transactional tables: concurrent DML on them causes severe lock contention.

Does the column order in a composite index matter?

Enormously. An index on (A, B) serves queries filtering on A, or on A and B, but not queries filtering on B alone - like a phone book sorted by surname is useless for finding first names. Lead with columns used in equality predicates and the most selective ones, and try to cover the query's WHERE and SELECT columns for an index-only scan.

What is a function-based index?

An index on an expression rather than a raw column, such as UPPER(last_name) or TRUNC(order_date). It makes predicates that apply a function to a column - which would otherwise hide the column from a normal index and force a full scan - use an index instead. The query's expression must match the index's expression exactly.

Can you have too many indexes?

Yes. Every index must be maintained on every insert, update of its columns, and delete, so excess indexes slow writes, consume space and redo, and give the optimizer more plans to evaluate. Removing unused indexes often speeds up write-heavy tables noticeably. Use Index Usage Tracking and invisible indexes to find and remove them safely.

How do I safely test dropping an index?

Make it invisible with ALTER INDEX ... INVISIBLE. The optimizer then ignores it (as if dropped) while Oracle still maintains it, so you can run your real workload for a period and watch for regressions. If nothing breaks, DROP it; if a query slows, ALTER INDEX ... VISIBLE brings it back instantly - far safer than dropping and hoping.