Lesson 2	Review of index types
Objective	Describe the index types that can be created in Oracle

Review of Oracle Index Types

Oracle Database performance depends heavily on choosing the right index structure for your workload. At a high level, Oracle supports the following widely used index families:

B-tree indexes (the default OLTP index type)
Bitmap indexes (analytics and low-cardinality filtering)
Reverse key indexes (specialized B-tree variant for hot insert patterns)
Function-based indexes (B-tree or bitmap indexes on expressions)

Conceptually, most traditional Oracle indexes are organized as a hierarchy of blocks: upper-level branch blocks guide lookups toward leaf blocks, and leaf blocks contain key values plus pointers (typically ROWIDs) to the table rows that satisfy the lookup predicate.

Index type	Structure	Uses
B-tree index	B-tree indexes store keys in sorted order using multiple levels of branch blocks and leaf blocks. A lookup compares the search key against branch-block entries while descending the tree, then reads leaf entries that contain the key value and row locator(s) (typically ROWIDs) for matching rows. Composite (concatenated) B-tree indexes store multi-column keys using the same structure.	Default choice for OLTP. Best for highly selective predicates (e.g., primary keys, unique keys), common equality lookups, and selective range scans. Also supports ordered retrieval when the index leading columns match the ORDER BY.
Bitmap index	Bitmap indexes represent each distinct key value as a bitmap where each bit position maps to a row. For predicates that combine multiple low-cardinality columns, Oracle can perform fast bitmap operations (AND/OR) on the bitmaps to compute the candidate row set before visiting table blocks.	Best for analytic workloads and data warehouses, especially on low-cardinality columns (status flags, categories, regions). Avoid for high-concurrency OLTP updates because bitmap maintenance can increase contention and locking side-effects.
Reverse key index	Reverse key indexes use a B-tree structure, but Oracle reverses the bytes of the index key before storing it (and reverses them back during access). This distribution helps spread inserts across many leaf blocks when keys are monotonically increasing.	Useful when you see “hot” right-edge leaf block contention from sequential inserts (for example, increasing numeric keys). Trade-off: reverse key indexes are generally not helpful for range scans on the original key because the stored order no longer matches the natural order.
Function-based index	A function-based index stores the computed result of an expression (for example, `UPPER(last_name)`, `TRUNC(order_date)`, or a deterministic expression). Oracle can use the index when the SQL predicate matches the indexed expression, avoiding recomputation across rows during query execution.	Use when applications filter or sort on expressions instead of raw column values (case-insensitive search, date bucketing, computed codes). Can be B-tree or bitmap depending on workload and cardinality.

Index Categories in Oracle

In Oracle 23ai, index selection is both broader and more automated than in older releases. You still choose index structures based on data shape and workload, but Oracle also offers features such as automatic indexing to propose and manage B-tree indexes when enabled.

B-tree indexes These are the standard Oracle index type and are excellent for primary keys and highly selective predicates. Common B-tree subtypes include:
1. Index-organized tables (IOT): Table data is stored in a B-tree structure keyed by the primary key, which can improve retrieval when most access paths use the primary key.
2. Reverse key indexes: A B-tree variant that reverses key bytes to reduce right-edge insert hot spots.
3. Descending indexes: Store one or more columns in descending order to help satisfy ORDER BY ... DESC without a sort in common access patterns.
4. Cluster indexes: Used with table clusters; the cluster key index points to blocks that contain rows sharing the same key, reducing I/O when multiple related rows are typically read together.
Bitmap indexes and bitmap join indexes Bitmap indexes map key values to row sets using bitmaps. Bitmap join indexes extend this idea by indexing a join result (typically star-schema patterns), enabling efficient filtering on dimension attributes while retrieving fact rows.
Function-based indexes Indexes on expressions, such as UPPER(col) or computed expressions used in predicates and ORDER BY. Depending on workload, these can be implemented as B-tree or bitmap indexes.
Application domain and specialized indexes Oracle supports extensible indexing, where an index implementation is tailored to a specific data domain. Common examples include:
- Oracle Text indexes for full-text search
- Oracle Spatial indexes for geospatial queries
- JSON search indexes to accelerate JSON-oriented filtering and path-based access patterns
- Vector indexes to support similarity search for vector data used in AI workloads
The key point is that “domain index” means the structure is optimized for the domain rather than a generic B-tree/bitmap layout.

In the next lesson, you will learn about descending indexes and when they make sense compared to a standard B-tree.

[1] B-tree: Oracle’s default index structure. Branch blocks guide lookups to leaf blocks, and leaf blocks store sorted key values plus row locators (typically ROWIDs) for matching table rows.

[2] Bitmap operations: Logical operations (AND/OR) performed on bitmap vectors to rapidly compute row sets that match combinations of predicates, before visiting table blocks.