Lesson 10

This module explained why indexes are central to database performance and scalability—and why “more indexes” is not the same thing as “better performance.” In Oracle 23ai, indexing is still grounded in the same fundamentals: choose the right access paths for your workload, keep statistics trustworthy, and manage index lifecycle (create, place, alter, rebuild, monitor, retire) as a disciplined part of physical design.

Module Workflow Summary

The lessons below form a single workflow that moves from core concepts to hands-on index management. Each page is listed here so you can quickly revisit the topic you need during design, troubleshooting, or refactoring work.

Lesson	Topic	Key takeaway
1	Introduction to Oracle indexes	Indexes are physical access structures that reduce I/O and improve response time when they match the workload.
2	Database index fundamentals	B-tree navigation + ROWID lookup is the classic “fast path” for selective predicates and joins.
3	Index types	Choose the right structure: B-tree, bitmap, function-based, partitioned, and specialized/domain variants.
4	Create an index	Build indexes where they help most: join keys, selective filters, and high-impact query patterns.
5	Separate indexes from tables	Physical placement matters: reduce contention by isolating index I/O from table I/O where architecture permits.
6	Alter index attributes	Rename, move, toggle visibility, adjust logging/parallelism, and manage index state without changing table meaning.
7	Rebuild an index	Rebuild/coalesce when it is justified by fragmentation, maintenance windows, or structural change—not by habit.
8	Data dictionary views	Use dictionary metadata to audit definitions, columns, placement, partitioning, and operational facts.
9	Drop an index	Index retirement is part of lifecycle management—drop only after validating dependency and performance impact.
10	Conclusion	Integrate indexing into your physical design methodology and treat index changes as production-grade change events.

What You Learned in This Module

By the end of this workflow, you can explain and perform the complete index lifecycle:

1. Why indexes are used (to reduce disk I/O and produce efficient access paths).
2. Which index types exist and how to choose them for the workload.
3. How to create an index with correct column selection and ordering.
4. Storage considerations (contention, tablespaces, and physical placement strategy).
5. How to alter an index (visibility, parallelism, logging, and operational attributes).
6. How and why to rebuild (maintenance actions tied to evidence, not superstition).
7. How to inspect index metadata (dictionary-driven auditing and diagnosis).
8. How to drop an index safely (dependency awareness + regression avoidance).

Index Concept Review

An index is an optionally created database object used primarily to increase query performance. The analogy to a book index still helps: a book index maps topics to a small set of page numbers so you can jump directly to relevant pages instead of scanning the entire book. In a database, an index maps key values to the row locations that contain those values.

The usefulness of an index depends on selectivity. If a predicate matches only a small fraction of rows, an index can avoid reading most table blocks. If a predicate matches a large fraction of rows, scanning the table (or using a different access path) may be cheaper.

A classic Oracle B-tree index stores key values alongside a row locator (ROWID). With a ROWID, Oracle can retrieve the target row with minimal additional work. This is why a selective index predicate often produces an execution plan shaped like:

• Index probe (find matching keys)
• ROWID lookup (fetch table rows)

If no usable index exists, Oracle must consider other options, including scanning many or all table blocks to locate qualifying rows. The cost-based optimizer chooses among these paths based on statistics and estimated cost.

You can build a database application without indexes, but you are effectively accepting poor performance as data volume grows. Indexes are one of the main mechanisms that keep response times stable as row counts move from thousands to millions (and beyond).

Question: If indexes are so important, why not index every column and every column combination?

Answer: Indexes are not free:

• Space: indexes consume storage and can bloat backups and maintenance workloads.
• DML overhead: inserts, updates, and deletes must maintain every affected index.
• CPU and memory: index maintenance consumes compute and buffer cache.
• Optimizer complexity: too many indexes can complicate plan selection and lead to unstable performance if statistics drift.

A poor indexing strategy wastes resources and can reduce performance overall. A correct indexing methodology is therefore part of physical database design: you choose indexes that support real query patterns and you manage those indexes throughout their lifecycle.

Improving Performance with Indexes

Consider the common case where a table grows rapidly. Without a supporting index, a selective lookup degenerates into repeated scanning. The example below is intentionally small so you can focus on the access pattern.

CREATE TABLE cust
(
  cust_id    NUMBER,
  last_name  VARCHAR2(30),
  first_name VARCHAR2(30)
);

As row counts climb into the millions, queries like this often slow down if Oracle must read too many table blocks to find matches:

SELECT cust_id, last_name, first_name
FROM   cust
WHERE  last_name = 'GOULD';

A typical physical-design response is to create an index that matches the predicate:

CREATE INDEX cust_last_name_ix
ON cust(last_name);

After creation, the optimizer can consider an index-based access path. Whether it will use the index depends on selectivity, statistics, and cost. This is why “create index” is never the end of the story—you must validate plans and measure performance.

Oracle 23ai Perspective: Treat Indexing as a Managed System

Modern Oracle environments emphasize controlled change and measurement. In practice, index work is safest when you:

1. Verify definitions and columns using dictionary views (what exists and how it is built).
2. Validate statistics (stale stats can produce confident but wrong optimizer decisions).
3. Stage risky changes (for example: invisible index testing before rollout or retirement).
4. Document outcomes (what changed, why, what improved, and what to watch).

Oracle also provides automation in indexing workflows. When available in your environment, automatic indexing can help identify candidate indexes and manage them as the workload evolves. Even when you rely on automation, the architect’s role remains essential: you still define constraints, validate critical plans, and confirm that index changes align with application intent.

Best-Practice Recap

• Index the “why,” not the “what.” Choose indexes that support real filters, joins, and ordering patterns—not hypothetical queries.
• Design composite indexes intentionally. Column order matters; lead with the most selective / most common predicate alignment.
• Separate logical and physical thinking. Normalize and constrain the model first; then add indexes as physical access paths.
• Prefer evidence-based maintenance. Rebuild and retire indexes based on monitoring, fragmentation symptoms, and measured regressions.
• Use the dictionary as your source of truth. Metadata is where indexing strategy becomes auditable and repeatable.

Glossary

Click the Quiz link below to answer a few questions about altering indexes.
Altering Indexes - Quiz