Lesson 1

A database is valuable not because it stores data, but because it can reliably retrieve the right data at the right time, often under concurrency, with predictable latency, and at scale. In Oracle Database 23ai, the optimizer has a wide set of access paths available to satisfy a query, and indexes are one of the most important. An index is a storage structure that allows Oracle to locate rows efficiently without scanning an entire table. In practice, indexes are the difference between a query that reads a few blocks and a query that reads millions.

Indexes also serve secondary goals: enforcing uniqueness (for example, a primary key or a unique constraint), supporting constraint validation, improving join performance, enabling selective filtering, and accelerating ordered access patterns. But indexes are not “free.” Every index is additional storage, additional metadata, and additional work during DML, because inserts, updates, and deletes on the table often require corresponding maintenance of the index structure. Oracle 23ai gives you many options to tailor how an index is stored and maintained, and understanding those options is part of being effective as a database administrator or developer.

This module introduces Oracle indexes with a practical mindset: you will learn what indexes are, why they exist, what they cost, and how Oracle uses them. You will also see how the index lifecycle fits into real-world operations: creating indexes, choosing appropriate index types, evaluating storage behavior, altering index attributes, and reorganizing indexes when necessary.

What You Will Learn in This Module

1. Why indexes are used and how they change query execution
2. The most common index types and when to use them
3. How to create indexes and interpret basic index metadata
4. Storage concepts: leaf blocks, branch blocks, and logical I/O
5. How to alter an index to change its storage and behavior
6. When to rebuild or coalesce an index and what each operation does
7. How to query the data dictionary for index details
8. How to drop an index safely and what the impact looks like

The goal is not to memorize syntax. The goal is to build a mental model: how Oracle stores index data, how the optimizer decides to use an index, and how your design choices affect performance under real workloads.

In Oracle Database 23ai, an index is an optional schema object created on one or more table columns (or expressions) that provides an efficient access path to table rows. Conceptually, the index stores key values in a structure that is optimized for fast lookup, and each key points to the location of one or more rows—typically through a ROWID reference. When Oracle can use an index to reduce the amount of data it must read, query performance can improve dramatically.

A key property of Oracle indexes is that they are logically and physically independent of the table data they reference. You can create or drop an index without changing the table’s row storage format. You typically do not need to rewrite SQL to “use” an index; the cost-based optimizer evaluates available indexes and chooses an access path based on statistics, selectivity, join structure, and many other factors. In other words, indexes should be designed so the optimizer can choose them when they are beneficial—without forcing application changes.

Most Oracle indexes you will encounter are B-tree indexes. A B-tree index organizes keys into a hierarchy of blocks: branch blocks (which guide navigation through the tree) and leaf blocks (which store the key entries and row references). The navigation cost is typically small and stable: Oracle can locate a key by reading a few index blocks, then follow a pointer to the table row. This is why index lookup performance often remains predictable even as a table grows—provided the index is selective and well-designed.

Indexes are especially valuable when queries frequently filter on a column (for example, a customer ID), join on a column (for example, a foreign key), or request rows in a specific order (for example, an index supporting ORDER BY without a separate sort). However, indexes are less helpful when a query must return a large percentage of the table. When selectivity is low, Oracle may prefer a full table scan because sequential reads can be more efficient than repeated index lookups plus table row fetches.

Oracle automatically maintains index entries as table data changes. This is both a feature and a cost. If you insert a row into a table, Oracle must also insert corresponding entries into relevant indexes. If you update an indexed column, Oracle may need to delete the old index entry and insert a new one. If you delete a row, Oracle must remove the index entries. This maintenance overhead is why “index everything” is not a best practice. The right approach is to index what the workload needs—and avoid indexes that provide little benefit.

Indexes, Relational Design, and Workload Intent

From a relational theory perspective, indexes do not change the logical model of your database. They do not define entity relationships or normalize data; they optimize physical access paths. But good relational design helps indexing succeed. For example, well-chosen primary keys and foreign keys establish stable join patterns. When join columns are consistent and selective, Oracle can leverage indexes to produce efficient nested loops joins or to accelerate constraint validation.

In practice, your indexing strategy should be derived from workload intent:

• OLTP workloads: many short transactions, selective lookups, high concurrency → indexes often critical.
• Analytics workloads: larger scans, aggregations, wide reporting → indexes help selectively, but full scans can dominate.
• Mixed workloads: requires careful indexing to avoid DML overhead while preserving query responsiveness.

What Comes Next

In the next lessons, you will build on this foundation by exploring why indexes are used, how Oracle chooses index access paths, and how to select appropriate index types (including composite indexes and specialized options such as reverse key behavior). You will also see the operational side of index management: altering index storage, reorganizing fragmented indexes, and validating index definitions through the data dictionary.