Oracle Database – Indexes and Execution Plans

Tuning an Oracle query is mostly one skill: getting the real execution plan, and comparing what the optimiser estimated against what actually happened. Everything else follows from that comparison. This post covers the index types, how to read a plan, and the handful of mistakes that account for most slow queries.

Index types you will actually use

CREATE INDEX orders_customer_ix   ON orders (customer_id);
CREATE INDEX orders_cust_date_ix  ON orders (customer_id, order_date DESC);
CREATE UNIQUE INDEX customers_email_uix ON customers (lower(email));
CREATE INDEX customers_upper_name_ix ON customers (upper(last_name));

-- Build without locking out DML, and gather stats while you are there
CREATE INDEX orders_status_ix ON orders (status) ONLINE COMPUTE STATISTICS;

Composite indexes: the leading-column rule

An index on (a, b, c) can be used for a predicate on a, on (a, b), or on (a, b, c). It is generally not usable for a predicate on b alone, because the entries are sorted by a first — like looking up someone in a phone book when you only know the first name.

CREATE INDEX ix ON orders (customer_id, order_date, status);

WHERE customer_id = 42                                  -- uses it
WHERE customer_id = 42 AND order_date > sysdate - 7     -- uses it, both columns
WHERE order_date > sysdate - 7                          -- generally cannot
WHERE customer_id = 42 AND status = 'NEW'               -- uses customer_id, filters status

So order the columns by how they are queried: equality predicates first, then the range predicate, then columns you only want along for the ride.

The last of those is a covering index. If every column the query touches is in the index, Oracle never visits the table at all — the plan says INDEX RANGE SCAN with no TABLE ACCESS BY INDEX ROWID under it, and that is usually the single biggest win available:

-- Answered entirely from the index
CREATE INDEX orders_cover_ix ON orders (customer_id, order_date, total);
SELECT order_date, total FROM orders WHERE customer_id = 42;

Oracle can also do an index skip scan, which probes the index once per distinct leading value — so a predicate on b alone is not impossible, just only viable when a has very few distinct values. Don't design around it.

Why your index is being ignored

1. A function on the indexed column

-- Index on last_name is unusable: the optimiser has no index on UPPER(last_name)
SELECT * FROM customers WHERE upper(last_name) = 'SMITH';

-- Index on order_date is unusable
SELECT * FROM orders WHERE trunc(order_date) = DATE '2024-03-18';

-- Fixes: rewrite so the column is bare...
SELECT * FROM orders
WHERE  order_date >= DATE '2024-03-18' AND order_date < DATE '2024-03-19';

-- ...or index the expression instead
CREATE INDEX customers_upper_ix ON customers (upper(last_name));

Implicit conversion counts as a function, and it is invisible. Comparing a VARCHAR2 column to a number makes Oracle apply TO_NUMBER to the column, killing the index:

-- account_no is VARCHAR2. This becomes TO_NUMBER(account_no) = 12345 → full scan
SELECT * FROM accounts WHERE account_no = 12345;
-- Quote it
SELECT * FROM accounts WHERE account_no = '12345';

Look for SYS_OP_C2C or a TO_NUMBER around a column name in the plan's predicate section — that is the fingerprint.

2. NULLs are not in a single-column B-tree index

Oracle does not store entirely-null keys. Two consequences:

-- Cannot use an index on status: the rows it wants are the ones not in the index
SELECT * FROM orders WHERE status IS NULL;

-- Cannot use a single-column index on total either — it might miss null rows
SELECT count(*) FROM orders;

-- Both work if the index cannot be all-null. A NOT NULL column, or a constant:
CREATE INDEX orders_status_ix ON orders (status, 1);

That , 1 is a real trick, not a joke: the second key is never null, so every row gets an index entry, and IS NULL becomes an index range scan.

3. A full scan is genuinely cheaper

Reading 40% of a table through an index means roughly one random I/O per row plus the index traversal; a full scan reads it in large sequential multi-block reads. The optimiser knows this. A full table scan is not a bug. On a 500-row lookup table it is always the right answer.

4. The statistics are wrong

The optimiser is a cost model fed by statistics. Stale statistics mean a plan chosen for a table shape that no longer exists — the usual cause of "it was fast yesterday".

-- When were these last gathered, and how far off is the row count?
SELECT table_name, num_rows, last_analyzed, stale_stats
FROM   user_tab_statistics WHERE table_name = 'ORDERS';

BEGIN
  dbms_stats.gather_table_stats(
    ownname          => user,
    tabname          => 'ORDERS',
    cascade          => TRUE,                       -- indexes too
    estimate_percent => dbms_stats.auto_sample_size,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO' -- histograms where they help
  );
END;
/

Histograms are what let the optimiser know that status = 'NEW' matches 200 rows while status = 'CLOSED' matches 40 million. Without one it assumes an even spread across distinct values and picks a single plan for both — which is wrong for one of them.

Getting the plan

EXPLAIN PLAN — the estimate

EXPLAIN PLAN FOR
SELECT c.last_name, sum(o.total)
FROM   customers c JOIN orders o ON o.customer_id = c.id
WHERE  o.order_date >= DATE '2024-01-01'
GROUP  BY c.last_name;

SELECT * FROM dbms_xplan.display(format => 'ALL +OUTLINE');

This is what the optimiser would do. It does not run the query, and — importantly — it does not peek at your bind values, so the plan it shows can differ from the plan you get.

DISPLAY_CURSOR — what actually happened

This is the one to use. Run the query with a hint that collects row counts, then ask for the plan with actuals beside the estimates:

SELECT /*+ GATHER_PLAN_STATISTICS */ c.last_name, sum(o.total)
FROM   customers c JOIN orders o ON o.customer_id = c.id
WHERE  o.order_date >= DATE '2024-01-01'
GROUP  BY c.last_name;

SELECT * FROM dbms_xplan.display_cursor(format => 'ALLSTATS LAST');

--------------------------------------------------------------------------------------
| Id | Operation                     | Name              | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                   |      1 |        |    842 |
|  1 |  HASH GROUP BY                |                   |      1 |    841 |    842 |
|* 2 |   HASH JOIN                   |                   |      1 |   1200 | 121043 |
|  3 |    TABLE ACCESS FULL          | CUSTOMERS         |      1 |    842 |    842 |
|* 4 |    INDEX RANGE SCAN           | ORDERS_DATE_IX    |      1 |   1200 | 121043 |
--------------------------------------------------------------------------------------

Compare E-Rows with A-Rows. That comparison is the whole technique. Above, the optimiser expected 1,200 rows from the index and got 121,043 — off by 100×. Every decision above that line was made on a bad number. Fix the estimate (statistics, a histogram, a rewritten predicate) and the plan usually fixes itself. Tuning by adding hints to a plan built on a wrong estimate is how you end up with SQL nobody can maintain.

Also read:

• Starts — how many times the operation ran. A nested loop with Starts = 121043 is doing 121,043 index lookups.
• A-Time and Buffers (in ALLSTATS) — where the work went. Buffer gets are a better signal than elapsed time because they don't vary with cache state.
• OMem/1Mem/Used-Mem and a 1 in the O/1/M column — a sort or hash that spilled to TEMP.
• The Predicate Information block below the table. access means the predicate drove the index; filter means rows were fetched and then thrown away. Turning a filter into an access predicate is often the whole fix.

In the client

SET AUTOTRACE ON EXPLAIN STATISTICS    -- SQL*Plus and SQLcl: plan + I/O per statement
SET TIMING ON

Bind variables

Every distinct SQL text gets parsed and stored in the shared pool. Literals make every execution a new statement:

-- Three statements, three hard parses, three plans in the shared pool
SELECT * FROM orders WHERE customer_id = 1;
SELECT * FROM orders WHERE customer_id = 2;
SELECT * FROM orders WHERE customer_id = 3;

-- One statement, parsed once, executed three times
SELECT * FROM orders WHERE customer_id = :id;

On a busy OLTP system, literal SQL causes latch contention in the shared pool and a measurable throughput ceiling. It is also, not coincidentally, how SQL injection happens. JDBC PreparedStatement and JPA both bind by default, so this is mostly a problem in hand-built dynamic SQL and in reporting tools.

The flip side: with one plan for all bind values, a skewed column can get a plan that suits status = 'CLOSED' and is disastrous for status = 'NEW'. Oracle mitigates this with bind peeking and adaptive cursor sharing, but if you have one genuinely skewed predicate, a literal there is a defensible choice.

Maintenance

-- Is anything using this index? Turn it invisible before you drop it.
ALTER INDEX orders_status_ix INVISIBLE;    -- optimiser ignores it, still maintained
ALTER INDEX orders_status_ix VISIBLE;      -- instant rollback if things get slower
DROP INDEX orders_status_ix;

-- Rebuild without blocking DML
ALTER INDEX orders_customer_ix REBUILD ONLINE;

-- Indexes on a table, and their selectivity
SELECT index_name, uniqueness, distinct_keys, num_rows, last_analyzed
FROM   user_indexes WHERE table_name = 'ORDERS';

SELECT index_name, column_position, column_name
FROM   user_ind_columns WHERE table_name = 'ORDERS'
ORDER  BY index_name, column_position;

INVISIBLE is the right way to retire an index: you get the performance effect of dropping it with a one-statement undo, and you never pay to rebuild a 40 GB index you dropped by mistake.

Remember indexes are not free. Every one of them is extra work on every INSERT, UPDATE of its columns, and DELETE. A table with twelve indexes has a slow write path, and some of those twelve are redundant prefixes of the others.

A short checklist

1. Get the real plan with GATHER_PLAN_STATISTICS and ALLSTATS LAST.
2. Find the first line where E-Rows and A-Rows diverge badly. That is the problem.
3. Check the statistics on the objects involved before touching anything else.
4. Look for functions and implicit conversions wrapped around indexed columns.
5. Check that every foreign key you join on has an index.
6. Only then consider a new index — and check USER_IND_COLUMNS first, because it may already exist with the columns in the wrong order.

Next

Transactions, isolation and locking — including the reason readers never block writers in Oracle, and the SKIP LOCKED trick that turns a table into a work queue.