Indexes, Execution Plans, and Tuning

B-tree indexes — the default#

Oracle's default index type is a B-tree, and it behaves the same way B-tree indexes do in any relational database (covered in more depth on this app's MySQL indexing page): a sorted structure that lets the database jump directly to matching rows instead of scanning the entire table.

CREATE INDEX idx_employees_department_id ON employees (department_id);

Oracle automatically creates a unique index backing every PRIMARY KEY and UNIQUE constraint — employees.employee_id and employees.email from the schema used throughout this track already have one each, with no separate CREATE INDEX needed. Everything else queried often — foreign key columns, columns in frequent WHERE clauses, columns used to JOIN or ORDER BY — is a candidate for an explicit index.

B-tree is the right default for high-cardinality columns (many distinct values relative to the row count) — a primary key, an email address, an employee ID. It's the wrong tool for the opposite case, which is exactly where Oracle's other major index type comes in.

Bitmap indexes — for low-cardinality, read-heavy data#

A bitmap index stores, for each distinct value in a column, a bitmap (one bit per row) marking which rows hold that value — instead of a B-tree's per-row pointer entries. That representation is extremely compact and extremely fast to combine with AND/OR against other bitmap indexes, which makes bitmap indexes the standard choice in data warehouse and reporting workloads filtering on a handful of low-cardinality columns simultaneously:

-- A reporting table where region only ever takes a handful of values
CREATE BITMAP INDEX idx_sales_region ON sales (region);
CREATE BITMAP INDEX idx_sales_channel ON sales (channel);

-- Oracle can combine both bitmaps directly to answer this efficiently
SELECT SUM(amount)
FROM sales
WHERE region = 'EMEA' AND channel = 'ONLINE';

A B-tree index on a column like region (perhaps 5 distinct values across millions of rows) barely helps — roughly a fifth of the table matches any single value, so the database often decides a full scan is cheaper than following an index anyway. A bitmap on the same column is compact and lets Oracle AND several conditions together at the bit level before ever touching a data block.

The trade-off is concurrency: updating a single row touched by a bitmap index requires locking the entire bitmap segment covering a range of rows, not just that one row's entry the way a B-tree update does — making bitmap indexes a poor fit for tables under heavy concurrent INSERT/UPDATE/DELETE traffic (classic OLTP), and a strong fit for read-heavy analytics and reporting tables that are bulk-loaded and queried far more than they're individually updated.

EXPLAIN PLAN and DBMS_XPLAN#

EXPLAIN PLAN asks Oracle how it intends to execute a query, without actually running it, and stores that plan in a special table (PLAN_TABLE) for inspection. DBMS_XPLAN.DISPLAY then formats it readably:

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 20;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Before an index on department_id, the plan looks roughly like this:

--------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Cost (%CPU)| Time  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   842 |    45   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   842 |    45   (0)| 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
   1 - filter("DEPARTMENT_ID"=20)

TABLE ACCESS FULL is a full table scan — Oracle reads every block of the EMPLOYEES table and filters afterward. Now add the index and check again:

CREATE INDEX idx_employees_department_id ON employees (department_id);

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 20;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | Rows | Cost (%CPU)|
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |   84 |     3   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES                   |   84 |     3   (0)|
|*  2 |   INDEX RANGE SCAN           | IDX_EMPLOYEES_DEPARTMENT_ID |   84 |     1   (0)|
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
   2 - access("DEPARTMENT_ID"=20)

INDEX RANGE SCAN means Oracle used the index to locate matching rows directly, then TABLE ACCESS BY INDEX ROWID fetched each matching row's full data using the ROWID the index handed back (see the data-types page for what ROWID actually is). Cost dropped from 45 to 3 — a rough, unitless estimate the optimizer uses to compare candidate plans, not a real time measurement, but directionally exactly what you'd hope to see after adding a useful index.

DBMS_XPLAN.DISPLAY_CURSOR goes a step further and reports the actual plan Oracle used for a query that already ran, including real row counts observed (not just the optimizer's estimate) — genuinely useful once the estimated plan and the query's real-world behavior seem to disagree:

SELECT * FROM employees WHERE department_id = 20;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

Partitioning, briefly#

Once a table grows into the tens or hundreds of millions of rows, even a well-indexed table can benefit from being partitioned — physically split into smaller, independently-managed segments based on a partitioning key, while still being queried as one logical table.

-- Range partitioning: one partition per year, common for time-series/log-style data
CREATE TABLE sales (
    sale_id   NUMBER,
    sale_date DATE,
    amount    NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p2025 VALUES LESS THAN (DATE '2026-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

• Range partitioning — splits by a range of values, most commonly a date column (one partition per month or year). The most common partitioning strategy by far, since it lets old data be archived or dropped one whole partition at a time instead of a slow row-by-row DELETE.
• List partitioning — splits by an explicit list of discrete values (e.g., one partition per region: 'EMEA', 'APAC', 'AMERICAS').
• Hash partitioning — splits rows evenly across a fixed number of partitions using a hash of the key, purely to spread I/O and reduce contention when there's no natural range or list to partition by.

The payoff that matters most in practice is partition pruning — a query filtering on the partitioning key (WHERE sale_date >= DATE '2025-06-01') only touches the partitions that could possibly contain matching rows, skipping the rest of the table entirely, which can turn a scan of a billion-row table into a scan of one month's worth of it.

Common mistakes#

• Creating a bitmap index on a column in a table that also receives heavy concurrent single-row INSERT/UPDATE traffic — the wider locking behavior of bitmap indexes under DML can turn what looks like a fast read-side win into serious write-side contention.
• Never running EXPLAIN PLAN/DBMS_XPLAN on a slow query and guessing at a fix instead — it takes seconds and tells you definitively whether an index is even being considered by the optimizer.
• Assuming any index automatically gets used — the optimizer might reasonably prefer a full table scan anyway when a query's filter isn't selective enough (matching a large fraction of the table), which is often the correct choice, not a bug.
• Partitioning a table by a column that queries rarely filter on — partition pruning only helps when the query's WHERE clause actually references the partitioning key; partitioning by the wrong column gets all the storage-management overhead with none of the query-performance benefit.