Oracle Index Types

An index is a schema object that accelerates SQL by providing a fast access path to table rows. In Oracle, indexes primarily improve performance for: highly selective predicates, join columns, ORDER BY / GROUP BY, and constraint enforcement (UNIQUE and PRIMARY KEY).

Indexes are not “free.” Every index adds write overhead during INSERT/UPDATE/DELETE and consumes storage. Good index design is about choosing the right index type for the workload and the data distribution.

Oracle supports a family of index structures. The two foundational structures you’ll see most often are: B-tree indexes and bitmap indexes. On top of those foundations, Oracle adds specialized variants such as function-based, partitioned, reverse key, and domain indexes (for text, spatial, and other extensible indexing).

A B-tree index (Oracle’s default index type) stores key values in sorted order and uses a balanced tree to locate a key quickly. A search begins at the root block, moves through branch blocks, and ends at the leaf blocks, which contain the key and a pointer to the row (typically a ROWID for heap tables).

B-tree indexes are the best general-purpose choice for high-cardinality columns and for queries that use equality or range predicates.

1. Root (top) block: entry point for the search; comparison keys route the search.
2. Branch (middle) blocks: guide traversal to the correct leaf range.
3. Leaf blocks: store the index entries and row pointers (ROWIDs for heap tables).
4. Row access: after finding the leaf entry, Oracle follows the pointer to fetch the row.

Practical design notes:

• Uniqueness: Oracle can create UNIQUE B-tree indexes (commonly for PRIMARY KEY / UNIQUE constraints) or non-unique indexes for general query acceleration.
• Composite (concatenated) indexes: a multi-column B-tree supports predicates that use the leading column(s). Column order matters.
• Clustering factor: if the table rows are physically scattered relative to the index order, index range scans may degrade into many random I/Os.

-- Default B-tree index
CREATE INDEX ix_orders_customer ON orders(customer_id);

-- Unique B-tree index (also created implicitly by UNIQUE / PRIMARY KEY constraints)
CREATE UNIQUE INDEX ix_customers_email ON customers(email);

-- Composite B-tree index: column order matters
CREATE INDEX ix_order_lines_lookup ON order_lines(order_id, line_numb);

Oracle provides multiple index types because workloads differ. The optimizer can only choose the best access path if you provide an index structure that matches your data and query patterns.

1. B-tree indexes: best for high-cardinality columns, OLTP workloads, primary keys, unique lookups, and range scans. They can also support ordered retrieval by index key.
2. Bitmap indexes: best for low-cardinality columns in read-mostly environments (data warehousing, analytics). Bitmap indexes are powerful for combining multiple predicates with AND/OR logic and for some COUNT() patterns. They are generally a poor fit for high-concurrency OLTP due to locking and maintenance characteristics.
3. Function-based indexes: store the result of an expression so the optimizer can use an index even when queries apply functions to columns. Common uses: case-insensitive search (UPPER/LOWER), computed values, and deterministic expressions.
4. Partitioned indexes (local / global): align index structure with table partitioning to enable partition pruning and reduce scan scope. Local indexes often simplify maintenance during partition operations.
5. Reverse key indexes: designed to reduce “hot blocks” for monotonically increasing keys (for example, sequence-based inserts). Great for insert concurrency, but they cannot support traditional index range scans.
6. Domain indexes (extensible indexing): specialized indexes managed by an index type implementation. Examples include Oracle Text (legacy name: interMedia Text), Oracle Spatial, and other application-defined index types.
7. Invisible indexes: indexes that exist physically but are ignored by the optimizer by default. They are useful for safe testing and for staged rollout of index changes.

A bitmap index stores a bitmap for each distinct key value. Each bit position maps to a row, and a bit value indicates whether the row contains that key. Because bit operations are fast, bitmap indexes can combine multiple low-selectivity conditions efficiently in analytic queries.

Examine the following definitions for additional information.

-- Bitmap index (best for low-cardinality columns in read-mostly workloads)
CREATE BITMAP INDEX bix_sales_region ON sales(region_code);

Rule of thumb: use bitmap indexes for analytics (many reads, fewer concurrent writes), and prefer B-tree indexes for OLTP.

A function-based index stores the result of a function or expression so the optimizer can use an index even when the query uses the same expression. This is a common solution for case-insensitive searches and computed predicates.

-- Case-insensitive lookup
CREATE INDEX ix_customers_email_uc ON customers(UPPER(email));

-- Query that can use the function-based index
SELECT customer_id
FROM customers
WHERE UPPER(email) = UPPER(:email);

Practical note: choose deterministic expressions and make sure application queries match the indexed expression.

A partitioned index is divided into partitions (local or global). When queries filter on the partition key, Oracle can prune partitions, which reduces the amount of index data that must be scanned.

-- Local partitioned index example (conceptual)
-- (Exact syntax depends on the table partitioning strategy)
CREATE INDEX ix_orders_local ON orders(order_date)
LOCAL;

Neighboring topic to keep in mind: partitioning strategy (range/list/hash/interval) strongly influences index choices and maintenance.

A reverse key index reverses the bytes of the indexed key. This spreads inserts across more leaf blocks and reduces contention for “rightmost leaf” hot spots (common with sequential keys).

-- Reverse key index (helpful for heavy insert concurrency with increasing keys)
CREATE INDEX ix_orders_id_rev ON orders(order_id) REVERSE;

Limitation: reverse key indexes are not suitable for range scans (for example, BETWEEN on an increasing numeric key).

An index-organized table (IOT) stores the table data in a B-tree structure ordered by the primary key. Secondary indexes on an IOT can exist, and Oracle can also use bitmap indexing with an IOT via a mapping mechanism.

A key concept is that IOTs use logical rowids. When bitmap (or other secondary) indexes exist on an IOT, Oracle may use a heap-organized mapping table to map between physical access structures and the logical rowid needed to reach the IOT row.

Bitmap indexes are not stored in sorted order, but in decision-support queries they can be extremely powerful when many conditions are applied and Oracle can combine bitmaps efficiently.

Click the quiz link below to answer a few questions about index types.
Index Types - Quiz
The next lesson shows how to create an index.

[1] low-cardinality columns: columns with relatively few distinct values compared to row count (for example, status flags, category codes, region codes). These often benefit from bitmap indexing in analytic workloads.