1. Introduction to Indexes

An index is a performance-optimizing structure that Oracle uses to minimize data access time by providing faster lookup capabilities. Instead of scanning the entire table to find rows matching a condition, Oracle can use the index to locate them more efficiently — just like using an index in a book instead of reading every page.

Indexes are especially useful in large databases, where query performance is critical and where access patterns involve filtering, joining, or sorting large volumes of data.

2. Why Use Indexes?

Indexes offer a multitude of performance and architectural benefits:

• Speeding Up Queries: When you issue a SELECT statement with a WHERE clause on an indexed column, Oracle can quickly retrieve the matching rows using the index rather than scanning the entire table. This drastically reduces query response time, especially for large tables with millions of records.
• Improving Join Efficiency: In join operations, indexes on foreign key and join columns reduce the number of comparisons needed by allowing Oracle to jump directly to matching rows.
• Supporting Sorting and Aggregation: Indexes help with ORDER BY, GROUP BY, and DISTINCT queries by reducing the amount of work Oracle has to do to sort or group data.
• Enforcing Uniqueness: Unique indexes prevent duplicate entries in key columns like email or username. Oracle automatically creates a unique index when you define a PRIMARY KEY or UNIQUE constraint.
• Enhancing OLAP (Analytical) Queries: Bitmap indexes in particular support complex analytical operations where queries may involve multiple columns with low cardinality.
• Reducing Table I/O: Oracle can fetch only the rows needed, reducing CPU and disk access, which improves scalability and overall database health.

3. How Indexes Work Internally

Understanding the internal structure helps in designing better indexing strategies.

• B-Tree (Balanced Tree) Structure: This is Oracle’s default and most common index type. It resembles a binary search tree but remains balanced to ensure predictable performance.
  • Root Block: The entry point to the index. It contains pointers to branch blocks and ensures efficient navigation through the tree.
  • Branch Blocks: Intermediate nodes that lead toward the leaf blocks. They contain key ranges and point to the correct leaf block containing the actual data.
  • Leaf Blocks: These hold the actual indexed key values along with ROWIDs pointing to rows in the table. This is where the index meets the actual data.
• ROWID (Row Identifier): Each index entry includes the ROWID, which identifies the physical location of the row in the table. Oracle uses this to perform a table access by ROWID, which is the fastest method of accessing a row.
• Index Scan: When executing a query using an index, Oracle performs an index scan that navigates the tree from root to leaf to find relevant rows.

4. Types of Indexes in Oracle

Oracle supports a variety of index types, each tailored for specific use cases and workloads:

B-Tree Index (Balanced Tree Index)

• Default index type in Oracle.
• Best suited for columns with high cardinality (i.e., many distinct values).
• Used for equality (=) and range (<, >, BETWEEN) searches.
• Supports fast lookups, sorting, and joining.

CREATE INDEX idx_emp_salary ON employees(salary);

Bitmap Index

• Ideal for low-cardinality columns such as gender, marital_status, or yes/no flags.
• Stores bits instead of row pointers; each bit represents a row.
• Excellent for complex ad hoc queries with multiple conditions (e.g., WHERE gender = 'M' AND status = 'Single').
• Frequently used in data warehousing, not suitable for high-concurrency OLTP systems due to locking issues.

CREATE BITMAP INDEX idx_emp_gender ON employees(gender);

Unique Index

• Ensures no duplicate values exist in the indexed column(s).
• Automatically created for PRIMARY KEY and UNIQUE constraints.
• Enforces data integrity at the storage level.

CREATE UNIQUE INDEX idx_emp_email ON employees(email);

Composite Index (Concatenated Index)

• Index on two or more columns.
• The order of columns matters: Oracle can only use the index efficiently if the query filters on the leading column(s).

CREATE INDEX idx_dept_empname ON employees(department_id, emp_name);

• WHERE department_id = ? — uses the index.
• WHERE emp_name = ? — does not use the index efficiently unless skip scan occurs.

Function-Based Index

• Useful when queries include expressions or functions, like UPPER(name) or TO_CHAR(date, 'YYYY').
• Oracle indexes the result of a function, not the raw column value.

CREATE INDEX idx_upper_name ON employees(UPPER(name));

This allows the index to be used even if the query includes the function in the WHERE clause.

Reverse Key Index

• Reverses the bytes of the indexed column’s values.
• Prevents index hot spots in columns with sequential data (like sequence values or timestamps).
• Commonly used in high-concurrency OLTP systems to spread inserts evenly across index blocks.

CREATE INDEX idx_empid_rev ON employees(emp_id) REVERSE;

Domain Index

• Used for custom data types such as spatial, text, or XML data.
• Managed by custom code (index type handlers).
• Requires Oracle’s extensible indexing framework.

Cluster Index

• Used with clustered tables, where related rows from different tables are stored together in the same physical block.
• One cluster index per cluster.

Partitioned Indexes

• Designed for use with partitioned tables.
• Local Indexes: One index per partition.
• Global Indexes: A single index across all partitions.
• Essential for managing very large tables and improving parallel query performance.

5. Creating and Managing Indexes – Syntax and Examples

Create Index

CREATE INDEX idx_salary ON employees(salary);

Drop Index

DROP INDEX idx_salary;

Rebuild Index

ALTER INDEX idx_salary REBUILD;

• Rebuilding reclaims space and refreshes statistics.
• Especially useful after mass deletions or large updates.

Make Index Unusable

ALTER INDEX idx_salary UNUSABLE;

Temporarily disables the index to avoid maintenance overhead during bulk inserts.

Rename Index

ALTER INDEX old_index_name RENAME TO new_index_name;

6. How Oracle Uses Indexes in Queries

Oracle uses the Cost-Based Optimizer (CBO) to decide whether to use an index.

Access Paths Using Indexes:

• Index Unique Scan: Used when the query returns a single row, typically with unique indexes and = predicates.
• Index Range Scan: Used for range queries (<, >, BETWEEN, LIKE 'ABC%'). Most common form of index scan.
• Index Skip Scan: Oracle can use a composite index even if the leading column isn’t in the WHERE clause — but only when the leading column has few distinct values.
• Fast Full Index Scan: Scans all index blocks but avoids sorting. Often used with ORDER BY.
• Index Full Scan: Reads all index entries in order, useful when index contains all needed columns.
• Table Access by ROWID: After finding matching entries in the index, Oracle uses ROWIDs to retrieve full rows from the table efficiently.

7. Index Behavior with DML Operations

Indexes speed up SELECT queries but add overhead to DML operations:

• INSERT:
  • Every insert must also insert a value into each applicable index.
  • More indexes = more overhead during data load.
• UPDATE:
  • Updating a column that is part of an index requires removing the old index entry and inserting a new one.
  • This increases redo and undo generation.
• DELETE:
  • Deletes must also remove entries from all indexes on the table.
  • Increases I/O cost.

Conclusion: Indexes increase read speed but can slow down writes. It’s essential to strike a balance between query performance and DML throughput.

8. Monitoring and Analyzing Index Usage

Oracle provides tools to track and analyze index usage:

Monitor Usage (Object Usage View)

ALTER INDEX idx_salary MONITORING USAGE;

After workload:

SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'IDX_SALARY';

Analyze Index Statistics

EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'IDX_SALARY');

Helps Oracle’s optimizer make informed decisions.

9. Best Practices for Indexing

• Index Selective Columns: Use indexes on columns that filter out a large portion of rows (i.e., high selectivity).
• Limit Number of Indexes: Too many indexes increase DML overhead and consume storage.
• Use Composite Indexes Wisely: Put the most selective column first. Oracle uses the index efficiently only when leading columns are filtered.
• Drop Unused Indexes: Use monitoring to identify unused indexes and remove them to reduce maintenance.
• Use Bitmap Indexes Only in Warehousing: Avoid them in transactional systems due to concurrency issues.
• Use Function-Based Indexes When Needed: Especially when your queries use functions or expressions in WHERE clauses.
• Regular Maintenance: Periodically rebuild or coalesce indexes, especially after bulk DML operations.

10. Pitfalls and When Not to Use Indexes

• Low-Selectivity Columns: Don’t index columns with few distinct values unless used in bitmap indexes.
• Highly Volatile Tables: Tables with frequent inserts/updates may suffer performance degradation due to index maintenance.
• Small Tables: For small datasets, a full table scan is often faster than using an index due to I/O optimization.
• Redundant Indexes: Don’t create separate indexes for individual columns already part of a composite index.
• Leading Column Missing: Avoid relying on indexes when queries do not include the first column of a composite index.