Lesson 1

Indexes are one of the primary tools for improving query performance in Oracle. In Oracle Database 23ai, indexing has expanded beyond traditional B-tree tuning into automation (Automatic Indexing), safer change testing (invisible indexes), online maintenance, and specialized index types for text, spatial, JSON, and AI vector workloads.

In this module, you will learn how to:

1. Describe the types of indexes that can be created in Oracle
2. Create a descending index
3. Describe the bitmap index improvements of Oracle
4. Create a reverse index
5. Describe two methods of creating statistics
6. Describe the new Oracle feature of domain indexes
7. Use the ONLINE parameter for indexes
8. Define function-based indexing
9. Create a function-based index

An index is an optional structure associated with a table (or table cluster) that can speed data access by reducing disk I/O. If a heap-organized table has no useful index for a predicate, Oracle may be forced into a full table scan—an approach that becomes increasingly expensive as row counts and table blocks grow.

Indexes tend to help most when:

1. Selectivity is high: predicates return a small percentage of the table’s rows (for example, finding a single department by ID).
2. Join and constraint paths are common: foreign-key columns are frequently joined or enforced, so indexing avoids heavy locking and reduces join cost.
3. Uniqueness must be enforced: a unique or primary key constraint needs a supporting index (Oracle will create one automatically unless you specify it).

A Quick Analogy

Imagine boxes of employee folders stored in random order. Without an index, you must open boxes and check folders one-by-one until you find the right employee. An index is a separate, ordered list that tells you exactly where to look.

ID 100: Box 3, position 1 (bottom)
ID 101: Box 7, position 8
ID 200: Box 1, position 10

Oracle’s indexing story is broader than “create a B-tree and call it done.” In 23ai, index enhancements can be thought of in four buckets:

1. More index choices for specific workloads
   Traditional B-tree indexes remain the default for OLTP predicates and joins, while bitmap indexes are typically used for low-cardinality analytics (common in data warehousing). Reverse key and descending indexes support particular access patterns described later in this module.
2. More ways to build the right index shape
   Function-based indexes let Oracle index expressions used in predicates (for example, UPPER(last_name)) rather than only raw column values—often eliminating expensive full scans caused by non-sargable predicates.
3. Less downtime during index maintenance
   Online index operations (using ONLINE) reduce disruption during index build/rebuild. Invisible indexes allow you to test the impact of adding or removing an index without changing application SQL—useful for controlled tuning experiments.
4. Automation and “index-like” search for modern data
   Automatic Indexing can create, validate, and manage indexes based on real workload patterns, reducing manual trial-and-error. For AI-driven applications, Oracle’s AI Vector Search introduces specialized indexes for similarity search over vector embeddings, enabling hybrid search alongside relational predicates.

1) Descending Index

Use a descending index when your workload frequently sorts or filters in descending order on a column and Oracle can use it to avoid extra sorting work.

CREATE INDEX hr.emp_hiredate_desc_idx
ON hr.employees (hire_date DESC);

2) Function-Based Index

Use a function-based index when SQL predicates apply a function to the column. This can turn an otherwise “non-sargable” predicate into an indexable lookup.

CREATE INDEX hr.emp_upper_lname_idx
ON hr.employees (UPPER(last_name));

3) Online Index Build/Rebuild

Use online operations when you need to minimize blocking in production during index maintenance.

CREATE INDEX hr.emp_deptid_idx
ON hr.employees (department_id)
ONLINE;

In the next lesson, we’ll classify index types (B-tree, bitmap, partitioned, function-based, domain indexes, and more) and connect each choice to the kind of SQL workload it optimizes.