Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 38 ยท Performance and Administration

Oracle Indexes: B-tree, Bitmap, Function-Based and More

Choose the right Oracle index: B-tree, composite, unique, function-based, bitmap and invisible indexes, plus when an index helps, when it hurts and how to monitor it.

An index is a separate structure that lets Oracle find rows without reading the whole table, like the index at the back of a book. The right indexes turn a query that reads millions of blocks into one that reads three. The wrong ones slow every INSERT, UPDATE and DELETE and waste storage. This lesson covers the index types you will actually use and how to decide between them.

Oracle cannot run on this site. Index DDL for Oracle is read-only. The runnable examples use SQLite, which supports B-tree, composite, unique and expression indexes, as stand-ins.

The B-tree index

The default index is a B-tree (balanced tree). Oracle stores sorted key values with the ROWID of each row (the row's physical address) in a tree of blocks: a root, branch blocks, and leaf blocks. A lookup walks from root to leaf in three or four block reads, even for a table with hundreds of millions of rows, then jumps to the table row by ROWID.

SQL
CREATE INDEX emp_last_name_ix ON employees (last_name);
CREATE UNIQUE INDEX emp_email_uq ON employees (email);

Oracle creates a unique index automatically for every PRIMARY KEY and UNIQUE constraint. A B-tree helps these predicates: equality (=), ranges (<, >, BETWEEN), prefix matches (LIKE 'Sm%'), and ORDER BY / MIN / MAX on the indexed column. It does not help LIKE '%son' (leading wildcard) or WHERE last_name IS NULL, because rows whose indexed columns are all NULL are not stored in a single-column B-tree index.

Composite indexes and column order

An index on several columns is composite. Column order matters, because the index is sorted by the first column, then the second within it:

SQL
CREATE INDEX emp_dept_sal_ix ON employees (department_id, salary);

This index serves WHERE department_id = 50, WHERE department_id = 50 AND salary > 5000, and WHERE department_id = 50 ORDER BY salary (no sort needed). It cannot be used efficiently by WHERE salary > 5000 alone, though Oracle may use an index skip scan when the first column has few distinct values. Rules of thumb: put equality columns first, then the range column, and put the more selective column first when columns are used equally often.

If an index holds every column the query needs, Oracle never visits the table, which makes it a covering index and often the cheapest plan.

SQL Column order in a composite index (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Function-based indexes

A function wrapped around a column hides it from an ordinary index: WHERE UPPER(last_name) = 'KING' cannot use an index on last_name. A function-based index stores the computed value:

SQL
CREATE INDEX emp_upper_name_ix ON employees (UPPER(last_name));

Now the query above can use it. The function must be DETERMINISTIC (see stored functions), and the query must use the same expression. Function-based indexes are also the way to index only some rows: the expression CASE WHEN status = 'OPEN' THEN order_id END stores keys only for open orders, because NULL results are not indexed. The index stays tiny while the table grows.

SQL Expression index (SQLite stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Bitmap indexes

A bitmap index keeps, for each distinct value, a bitmap with one bit per row. It shines on low-cardinality columns (gender, status, region) in read-mostly data warehouses, because Oracle can combine several bitmap indexes with fast bitwise AND / OR before touching the table:

SQL
CREATE BITMAP INDEX cust_region_bix ON customers (region);

Never use bitmap indexes on tables with concurrent DML (an OLTP system): a single row change locks a whole range of bitmap entries, so sessions block each other. B-tree indexes are right for OLTP.

Other index options worth knowing

Option Use
REVERSE key index Spreads sequential key inserts across leaf blocks to reduce contention (rarely needed today).
DESC columns Matches ORDER BY ... DESC.
INVISIBLE index Exists and is maintained but ignored by the optimizer, perfect for testing a drop (ALTER INDEX ix INVISIBLE).
COMPRESS Prefix compression shrinks indexes with repeating leading values.
LOCAL / GLOBAL Partitioned-table indexes. Local ones follow the table partitions.
Index-organized table The whole table stored in a B-tree on its primary key.
Text and spatial CTXSYS.CONTEXT and spatial indexes for full-text and geometry search.

Costs and pitfalls

Every index must be updated by every INSERT, DELETE and by any UPDATE of an indexed column, and takes disk and buffer cache space. Do not index everything. Index columns that are:

  • used in WHERE, JOIN and ORDER BY of frequent, selective queries,
  • foreign keys (an unindexed foreign key makes parent deletes lock the child table and slows joins),
  • not wrapped in functions, implicit conversions (WHERE varchar_col = 123 converts the column and ignores the index), or leading wildcards.

An index is also ignored when the optimizer judges a scan cheaper, such as when the query returns a big fraction of the table. Remove indexes that are never used: ALTER INDEX ix MONITORING USAGE (or look at DBA_INDEX_USAGE in 12.2 and later) shows which ones are touched. Rebuilding indexes on a schedule is rarely necessary; Oracle's B-trees stay balanced, and ALTER INDEX ... REBUILD ONLINE is for specific fragmentation cases after mass deletes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why can WHERE UPPER(last_name) = 'KING' ignore a normal index on last_name?

    The index is sorted by the raw column value. A function-based index stores UPPER(last_name) instead.
  2. 2. For an index on (department_id, salary), which query benefits most?

    The leading column is used for equality and the second for the range.
  3. 3. Where do bitmap indexes belong?

    Bitmap entries lock in large ranges, so concurrent DML blocks; they excel at combining low-cardinality filters in reporting.
  4. 4. What is an INVISIBLE index?

    It lets you test the effect of dropping an index without dropping it.

Summary

  • B-tree indexes are the default; they serve equality, range, prefix and ordering needs and back every primary and unique key.
  • Composite index order matters: equality columns first, then ranges; covering indexes avoid table access.
  • Use function-based indexes for expressions, bitmap indexes only in read-mostly warehouses, and invisible indexes to test removals.
  • Every index costs write speed and space: index foreign keys and selective filters, and drop unused ones.

Next: SQL Tuning and Hints