A query is slow, so someone adds an index. Sometimes it’s instant magic. Sometimes nothing changes and the index just sits there slowing down every insert. And sometimes the query gets slower. All three happen with the same statement, the same table, the same index — because an index is never fast or slow on its own. It’s fast or slow for a given query’s selectivity, and the only way to know which is to read the plan.
The reflex — “it’s slow, add an index” — is right often enough to be dangerous. This post is the model underneath it: what an index actually costs, the one number that decides whether it helps, the three outcomes (help, ignore, hurt), and a lab that runs the same table five ways and measures each — a selective query dropping from a 17,834-buffer full scan to 14 buffers with an index, and a forced index reading more than the scan it replaced.
What an index actually costs
An index is a second, sorted copy of one or more columns, kept permanently in step with the table. That buys you one thing: the ability to find a small set of rows without reading the whole table. It costs you two. First, every write pays: an insert, a delete, or an update of an indexed column has to maintain the index too, so indexes make writes slower and use space. Second — the subtle one — an index only helps a read that’s selective enough to be worth the indirection.
Here’s the indirection. A normal (non-covering) index doesn’t hold your whole row, just the indexed column plus
a rowid. So to answer SELECT ... WHERE indexed_col = x, Oracle walks the index to find the matching rowids,
then visits the table once per row to fetch the other columns — the INDEX RANGE SCAN followed by TABLE ACCESS BY INDEX ROWID you see in plans. That’s cheap when “the matching rows” is a handful. It’s a catastrophe when
it’s most of the table, because you’ve turned one sequential scan into millions of scattered single-row visits.
The one number that decides: selectivity
Selectivity is just what fraction of the table matches your predicate. It’s the whole game. A predicate that matches a few rows is a perfect fit for an index; a predicate that matches most rows is a perfect fit for a full scan; and the crossover is lower than people expect — often just a few percent, because scattered indexed reads are so much more expensive per row than a sequential scan.
The optimizer’s job is to estimate that fraction and pick accordingly, which is why this topic sits on top of two others: it needs accurate statistics — especially a histogram on a skewed column — to know the fraction, and you read its decision in the execution plan. Give it a good histogram and it will use an index for a rare value and ignore that same index for a common one, in the same table, minutes apart. That’s not the optimizer being inconsistent; that’s it being right.
flowchart TD
Q["Query: WHERE on an indexed column"] --> S{"How selective?<br/>what fraction of rows match?"}
S -->|"few rows<br/>customer_id = 500"| I["INDEX RANGE SCAN<br/>→ 14 buffers (huge win)"]
S -->|"most rows<br/>status = SHIPPED (95%)"| F["TABLE ACCESS FULL<br/>→ 17,818 buffers<br/>optimizer ignores the index — correctly"]
F -.->|"force the index anyway"| H["INDEX RANGE SCAN over 95% of rows<br/>→ 19,459 buffers (MORE than the scan)<br/>the index hurt"] 
Help, ignore, hurt — measured
Take a million-row ORDERS table. customer_id has about 100,000 distinct values (very selective — roughly ten
rows each). status is heavily skewed: 950,000 SHIPPED, 49,000 OPEN, 1,000 PENDING. Index both columns,
gather a histogram on status, and run five queries, reading the real access operation and buffer gets from each:
| Query | Plan | Buffer gets |
|---|---|---|
WHERE customer_id = 500 (natural) | INDEX RANGE SCAN | 14 |
WHERE customer_id = 500 (FULL hint) | TABLE ACCESS FULL | 17,834 |
WHERE status = 'SHIPPED' — 95% (natural) | TABLE ACCESS FULL | 17,818 |
WHERE status = 'SHIPPED' (INDEX hint) | INDEX RANGE SCAN | 19,459 |
WHERE status = 'PENDING' — 0.1% (natural) | INDEX RANGE SCAN | 24 |
Three lessons fall straight out of those numbers:
- The right index is an enormous win. The selective
customer_idquery reads 14 buffers with the index versus 17,834 scanning the table — roughly 1,270× less work for the same answer. This is the case the reflex is right about. - The optimizer ignores a useless index on purpose. For
SHIPPED— 95% of the table — it picks a full scan and leavesidx_statusuntouched, because reading almost every row through the index would be slower than scanning. That index isn’t helping the common-value query at all; it’s pure write overhead for it. (It does earn its keep forPENDING, below — which is exactly why it’s not simply “a bad index.”) - A forced index can actively hurt. Make the
SHIPPEDquery useidx_statusand it reads 19,459 buffers — more than the 17,818 of the full scan it replaced. That is the “I added an index and the query got slower” bug reports, reproduced and measured. The index didn’t help; it added indirection on top of reading the whole table.
And the punchline is the same idx_status index appearing in both the ignore and the help rows: 17,818
buffers as a full scan for the 95% value, 24 buffers as an index range scan for the 0.1% value. Nothing about
the index changed between those two queries. Only the selectivity did.
Don’t take my word for it — run it. The indexes lab builds the million-row skewed table on Oracle Database Free, gathers the histogram, and runs all five queries with
GATHER_PLAN_STATISTICS. It asserts the selective query gets an index range scan and is far cheaper than the full scan, thatSHIPPEDgets a full scan whilePENDINGgets an index range scan off the same index, and that forcing the index onSHIPPEDreads more buffers than the full scan. If any of those flips, the run fails. Proven on every CI push.
B-tree, bitmap, and covering scans
Three things worth knowing beyond “add an index”:
- B-tree is the default and almost always right for OLTP and selective lookups. It’s what both indexes above are.
- Bitmap indexes suit low-cardinality columns in read-mostly/DW workloads — a
statuswith three values is the textbook case — and they combine well across columns (AND/OR of bitmaps). But bitmaps lock badly under concurrent DML, so they’re wrong for a busy OLTP table. The point isn’t “use bitmap forstatus”; it’s that low cardinality is the signal to stop and think about workload, not to reach for a plain B-tree reflexively. - A covering (index-only) scan skips the table entirely. If every column a query needs is in the index,
Oracle answers from the index alone — no
TABLE ACCESS BY INDEX ROWID— which is why the exact columns and their order in an index matter, and why a well-chosen composite index can beat a single-column one for a specific query.
What teams get wrong
- “It’s slow, add an index” without checking selectivity. If the predicate isn’t selective, the index won’t be used (best case) or will make things slower if forced (worst case) — and either way it taxes every write. Estimate the fraction of rows first; reach for an index when it’s small.
- Reading “the optimizer isn’t using my index” as a bug. Usually it’s the optimizer being right: the predicate isn’t selective enough, or the stats say so. Before you hint it into submission, check the stats and the actual selectivity — you may be about to force the slower plan.
- Missing histograms on skewed columns. Without a histogram the optimizer assumes values are uniform, so it can’t tell a common value from a rare one and makes the same (wrong) call for both. On any column that’s both skewed and filtered, a histogram is what lets the index be used where it helps and ignored where it doesn’t.
- Indexing every column “to be safe.” Each index is permanent write overhead and space. Unused indexes
(which you can find with the same privilege-analysis mindset — measure
actual usage via
V$OBJECT_USAGE/monitoring) are pure cost; drop them. - Forgetting that a local index aligns with partitioning. On a partitioned table, a local index keeps partition maintenance fast and prunes with the table; a global index can invalidate on a partition drop. Pruning narrows which partitions, the index narrows which rows within them — they work together.
- Composite index column order as an afterthought. Order the columns by how queries filter (equality predicates first, then ranges), and consider covering the columns a hot query selects so it can skip the table visit entirely. The wrong order makes the index unusable for the very query you built it for.
Frequently asked questions
When does an Oracle index help a query, and when does it hurt?
An index helps when the query is selective — it matches a small fraction of the table — because Oracle can walk the index to a few rowids and visit only those rows instead of scanning everything. It hurts, or is simply ignored, when the predicate matches a large fraction of the table: reading most rows through an index means many scattered single-row table visits, which is slower than one sequential full scan, so the optimizer chooses the full scan. If you force the index in that case it can read more than the full scan it replaced. Separately, every index adds overhead to inserts, updates, and deletes and consumes space, so an index that no query benefits from is pure cost. The deciding factor is selectivity, and the execution plan (INDEX RANGE SCAN versus TABLE ACCESS FULL, and the buffer gets) shows which case you are in.
Why is the optimizer not using my index?
Most often because it is correct not to. If the predicate matches a large share of the rows, a full table scan is genuinely cheaper than using the index, so the optimizer ignores the index on purpose. Other common reasons: the statistics are stale or missing a histogram, so the optimizer misjudges how many rows match; the indexed column is wrapped in a function or an implicit type conversion in the WHERE clause, which hides it from the index; the leading column of a composite index is not in the predicate; or the index is unusable or on a different column than you think. Before hinting the index into use, check the real selectivity and the statistics — forcing an index the optimizer rejected frequently produces the slower plan, not the faster one.
How selective does a predicate have to be for an index to help?
There is no single threshold, but the crossover is lower than most people expect — often in the low single-digit percent of the table, and sometimes a fraction of a percent for a wide table. The reason is that reading a row through an index is far more expensive per row than reading it in a sequential full scan, because indexed access produces scattered single-block reads and revisits the table for each matching row. So once a query matches more than a small slice, the accumulated cost of all those individual visits exceeds the cost of one full scan. The exact point depends on the row size, clustering factor, and block size, which is why the optimizer computes it from statistics rather than using a fixed rule — and why you let it decide with good stats rather than guessing.
What is a covering or index-only scan?
A covering scan, which Oracle performs when a query's every referenced column is present in the index, answers the query from the index alone and skips the table visit (the TABLE ACCESS BY INDEX ROWID step) entirely. Because the table is never touched, it can be dramatically cheaper than an ordinary index range scan for the same predicate, especially when many rows match. This is why the exact columns in an index, and their order, matter: adding the one or two columns a hot query selects to an existing index can turn a range-scan-plus-table-visit into an index-only scan. The trade-off is that a wider index costs more to maintain and store, so you cover the columns of specific important queries rather than everything.
What is the difference between a B-tree and a bitmap index?
A B-tree index, the default, stores sorted key values each pointing at a rowid and is well suited to selective lookups and ranges in both OLTP and analytic workloads. A bitmap index stores, for each distinct value, a bitmap marking which rows have it, which makes it very compact and efficient for low-cardinality columns and for combining multiple such columns with AND/OR — the typical data-warehouse pattern. The critical caveat is concurrency: updating a bitmap-indexed value locks a whole segment of the bitmap, so bitmap indexes are a poor fit for tables with concurrent DML and are generally reserved for read-mostly or bulk-loaded data. For a low-cardinality column on a busy transactional table, neither a plain B-tree (which the optimizer will often ignore) nor a bitmap (which locks badly) is an automatic answer — the right move is to look at the workload and the queries.
Do indexes slow down inserts and updates?
Yes. Every index on a table must be kept consistent with the table, so an insert has to add an entry to each index, a delete has to remove entries, and an update has to maintain the index for any indexed column it changes. That maintenance is real write cost and additional space and redo, and it grows with the number of indexes. This is why indexing every column defensively is a mistake: indexes that no query uses provide no read benefit while taxing every write. The practical discipline is to index for the queries that actually run, monitor which indexes are used, and drop the ones that are not — the same measure-then-remove approach you would apply to over-granted privileges.
Why does forcing an index sometimes make a query slower?
Because for an unselective predicate the index adds work rather than removing it. When most rows match, using the index means Oracle walks the index and then performs a table visit for each of those many rows, producing large numbers of scattered single-block reads, whereas a full table scan reads the table sequentially in fewer, larger operations. Forcing the index therefore replaces one efficient scan with an index traversal plus millions of row visits, which can read more blocks in total than the scan it replaced — in the companion lab, forcing the index on the 95% value reads 19,459 buffers versus the full scan's 17,818. The lesson is that an INDEX hint is not a speed-up button; it overrides the optimizer's cost comparison, and on an unselective query that comparison was correctly favoring the full scan.
Indexes are the same performance discipline as the rest of the series: understand what the optimizer is actually doing and give it what it needs to do the fast thing, instead of reaching for a reflex. Reading the execution plan is where you see an index range scan or a full scan and the buffer gets that settle the argument; good statistics and histograms are what let the optimizer judge selectivity correctly; SQL plan baselines keep a good plan from regressing; and partition pruning narrows which segments are read before the index narrows the rows within them. Index for the queries you actually run, check selectivity before you add or force one, keep histograms on skewed columns, and drop the indexes nothing uses. Then prove it the way that ends the argument — with the indexes lab, where the same index is a 1,270× win, a no-op, and a slowdown depending on one number.
Have a question or some feedback?
I write here in a personal capacity and enjoy comparing notes with other Oracle folks. Say hello.
Get in touch