Oracle Database Indexes

An index is a schema object that stores keys (column values, or expressions based on column values) in a structure optimized for fast lookup. In Oracle Database 23ai, indexes exist to reduce the work required to locate rows: fewer blocks read, fewer comparisons performed, and fewer rows examined to satisfy a predicate or join.

Indexes are typically “transparent” to the application because SQL does not need to reference them explicitly. The Oracle optimizer evaluates available access paths—full table scans, index scans, joins, sorts, and more—and chooses a plan based on statistics and cost. Your job when “applying indexes” is to design index definitions that match workload intent, so that Oracle can select the index when it is genuinely cheaper than scanning the table.

If table data is stored in segments and blocks, why do we need indexes? Because the default access path for a large table is expensive: a full scan reads many blocks, often to retrieve only a small subset of rows. When a query is selective, an index can act as a shortcut: it narrows the search quickly and directs Oracle to the exact row locations with minimal I/O.

The root of data access performance is the number of blocks Oracle must read and process. Modern systems may use SSD and caching layers rather than a mechanical disk head, but the same principle applies: block reads (physical or logical) are the dominant cost driver for most OLTP-style queries.

Oracle distinguishes between:

• Logical I/O (buffer cache reads, “consistent gets” and “db block gets”)
• Physical I/O (reading blocks from storage into the buffer cache)

Indexes reduce work by minimizing the number of blocks required to identify qualifying rows. Even when the required blocks are already cached (logical I/O only), fewer block visits typically means lower CPU and faster response time.

How indexes help Indexes reduce block visits in three primary ways:

1. Selective filtering: If a predicate matches a small fraction of rows (high selectivity), an index can locate the rowids of qualifying rows without scanning the table.
2. Efficient navigation: B-tree indexes are ordered structures. Oracle can traverse branch blocks to locate a key range quickly, then walk leaf blocks for the qualifying key values.
3. Ordered retrieval: Because B-tree indexes store keys in order, Oracle may satisfy ORDER BY or range predicates with less sorting work (or no sort) when the index order matches the query requirements.

Indexes are most effective when they enable Oracle to read a small number of index blocks plus a small number of table blocks. When a query requires a large percentage of rows, Oracle may prefer a full table scan because sequential reads can be cheaper than many random rowid lookups.

Also remember the cost side of the equation: each additional index introduces overhead for inserts, updates, and deletes. A well-indexed OLTP system is not one with “many indexes,” but one with the right indexes.

“Applying indexes” means aligning index definitions with the way SQL statements filter, join, and sort data. The best starting point is always the workload: the WHERE clauses and JOIN predicates that occur most frequently, and the queries where latency matters.

In Oracle, common index access patterns include:

• Index unique scan: equality on a unique key (often a primary key)
• Index range scan: ranges, leading-column matches for composite indexes, and ordered retrieval
• Index full scan: ordered traversal of an entire index (can satisfy ordering requirements)
• Index fast full scan: reads index blocks like a table (often used for count/aggregation when table access is unnecessary)
• Skip scan: can sometimes use a composite index even when the leading column is not supplied (depends on cardinality)

The practical lesson: you choose an index definition that enables the intended access path. If your queries filter by auction_id, then an index on auction(auction_id) creates the option for an index scan instead of a table scan.

SELECT start_time, stop_time
FROM auction
WHERE auction_id = 7;

SELECT start_time, stop_time
FROM auction
WHERE auction_id = 7;

SELECT start_time, stop_time
FROM auction
WHERE auction_id = 7;

Oracle B-tree indexes store keys in a balanced structure. Oracle navigates from the root through branch blocks to leaf blocks that contain key entries. Leaf entries contain the key value plus row location information (typically a ROWID for heap tables). The key point is that Oracle can locate the relevant leaf block with a small, bounded number of block visits, then scan only the key range that qualifies.

This model also explains why indexes are not always chosen. After Oracle finds the matching index entries, it may still need to fetch table blocks by rowid. If the table rows are scattered across many blocks, rowid lookups can become random and expensive. This is where the concept of clustering factor becomes important: it reflects how well the table’s row order aligns with the index key order. A poor clustering factor often means more table blocks must be read for a range scan.

In Oracle 23ai, the optimizer’s decision to use an index depends heavily on statistics. Accurate statistics allow the optimizer to estimate selectivity and cost. When statistics are stale or missing, Oracle can make poor choices—either scanning when an index would be better, or using an index when scanning would be better.

Practical Rules for Applying Indexes

• Index columns used frequently in selective predicates. Equality predicates on high-cardinality columns are classic index candidates.
• Support joins with indexes on foreign keys. Indexing foreign keys reduces lock contention and improves join performance in many OLTP systems.
• Build composite indexes to match real predicates. Put the most selective (and most commonly used) column first when possible, but design based on query patterns—not slogans.
• Avoid redundant indexes. Multiple indexes that share the same leading columns often duplicate cost with limited benefit.
• Don’t over-index write-heavy tables. Each index adds DML overhead; keep only what the workload justifies.

Oracle supports multiple index types. B-tree indexes are the default and are effective for most OLTP access patterns. Oracle also provides specialized options to handle distinct workloads and data distributions.

• B-tree indexes: General-purpose, support equality and range predicates.
• Bitmap indexes: Often used in analytics and data warehousing for low-cardinality columns (not typically used for highly concurrent OLTP updates).
• Function-based indexes: Index expressions (for example, UPPER(last_name)) so queries can use an index even when applying functions in predicates.
• Unique indexes: Enforce uniqueness (commonly used to implement primary key and unique constraints).
• Partitioned indexes: Local or global indexing aligned to partitioned tables for manageability and scale.

Oracle also provides operational capabilities that help you evolve indexing safely:

• Invisible indexes: Mark an index invisible to test impact without dropping it (optimizer ignores it unless explicitly enabled for the session).
• Index monitoring: Track usage for candidate cleanup decisions (commonly via V$OBJECT_USAGE / USER_OBJECT_USAGE for monitored indexes).
• Automatic Indexing (where available): In supported editions/configurations, Oracle can create and validate indexes automatically under policy control, with rollback if an index is not beneficial.

Indexes behave differently depending on table organization. Most tables are heap-organized (rows stored in insertion/space-available order), where B-tree index entries typically point to heap rows via ROWID. Oracle also supports index-organized tables (IOTs), where the table data itself is stored in a B-tree structure keyed by the primary key.

IOTs can reduce I/O for key-based access because the table data is clustered by primary key. However, secondary indexes on IOTs can introduce additional maintenance and rowid semantics. Oracle tracks the quality of “direct access” from secondary index entries to data blocks using the PCT_DIRECT_ACCESS column (for applicable index types). This value can help you detect when secondary index row references have become less direct over time.

You can query index metadata to review this indicator:

SQL> SELECT index_name, index_type, pct_direct_access
  2  FROM user_indexes;

INDEX_NAME                     INDEX_TYPE   PCT_DIRECT_ACCESS
------------------------------ -----------  -----------------
EMPLOYEES_IOT_PK               IOT - TOP    0
EMPLOYEES_PART_1I              NORMAL       100

When PCT_DIRECT_ACCESS falls below 100 (for the relevant index types), it indicates that the optimizer’s physical guess for direct block access is becoming less effective, which may increase I/O during access via secondary indexes. A sustained drop can be a signal to consider maintenance.

Two common approaches are used to refresh secondary index access paths for IOT-related scenarios:

1. Rebuild the secondary index to re-pack the structure and refresh stored references.
2. Update block references (when supported) to refresh the physical guesses without a full rebuild.

A rebuild uses the standard syntax:

ALTER INDEX employees_part_1i REBUILD;

Updating block references (commonly used for IOT secondary index maintenance) uses:

ALTER INDEX employees_part_1i UPDATE BLOCK REFERENCES;

These operations illustrate an important operational principle: indexes are performance structures, and they sometimes need maintenance. The right maintenance choice depends on the symptom (fragmentation, contention, stale references) and the operational constraints (availability, redo/undo impact, maintenance windows).

The following link takes you to a section that contains additional images and details: Oracle Data Index

The next lesson explores the basic types of index structures and how to choose between them.