Oracle Indexes: Complete Guide for Oracle DBAs – Types, Examples, Best Practices & Performance Tuning

Introduction

Indexes are one of the most powerful performance optimization features in Oracle Database. They enable Oracle to locate rows quickly without scanning an entire table, significantly reducing I/O and improving SQL query performance.

However, creating too many indexes or choosing the wrong index type can increase storage requirements, slow down INSERT, UPDATE, and DELETE operations, and make maintenance more complex.

This guide explains Oracle Indexes from a DBA perspective, covering index types, real-world use cases, monitoring, maintenance, best practices, and common mistakes.

What is an Oracle Index?

An Oracle Index is a schema object that stores pointers to table rows, allowing Oracle to retrieve data much faster than performing a Full Table Scan.

Without an index, Oracle reads every block in the table.

With an index, Oracle reads only the required blocks.

Benefits

• Improves SQL query performance
• Reduces disk I/O
• Faster data retrieval
• Better optimizer execution plans
• Improves join performance

Oracle Index Architecture

Application

      │

      ▼

SQL Query

      │

      ▼

Oracle Optimizer

      │

      ▼

Oracle Index

      │

      ▼

Required Table Blocks

1. B-Tree Index

What is it?

The default and most commonly used Oracle index.

Best For

• Primary Keys
• Unique Keys
• Equality searches
• Range searches
• OLTP databases

Example

CREATE INDEX IDX_EMP_NAME

ON EMPLOYEES(LAST_NAME);

Advantages

✔ Fast lookup

✔ Supports ORDER BY

✔ Supports BETWEEN

✔ Excellent for high-cardinality columns

Avoid

Not beneficial for very small tables.

2. Bitmap Index

What is it?

Stores bitmap values instead of row pointers.

Best For

• Data Warehouse
• Reporting
• Low-cardinality columns

Examples

• Gender
• Status
• Department

CREATE BITMAP INDEX IDX_STATUS

ON ORDERS(STATUS);

Advantages

✔ Excellent compression

✔ Fast aggregation

✔ Excellent for reporting

Avoid

Do not use in high DML environments.

3. Function-Based Index

Indexes the result of a function.

Without it, Oracle usually performs a Full Table Scan.

Example

CREATE INDEX IDX_UPPER_NAME

ON EMPLOYEES(UPPER(LAST_NAME));

Query

SELECT *

FROM EMPLOYEES

WHERE UPPER(LAST_NAME)='SMITH';

Best For

• UPPER()
• LOWER()
• NVL()
• SUBSTR()
• TRUNC()

4. Reverse Key Index

Stores keys in reverse order.

CREATE INDEX IDX_REV

ON EMPLOYEE(EMP_ID)

REVERSE;

Best For

• Oracle RAC
• High insert applications
• Sequential primary keys

Benefits

✔ Prevents hot blocks

✔ Better insert distribution

5. Invisible Index

Oracle ignores the index until enabled.

CREATE INDEX IDX_TEST

ON EMP(SALARY)

INVISIBLE;

Use Cases

• Performance testing
• Safe index evaluation
• What-if analysis

6. Local vs Global Partitioned Indexes

Local Index

Each table partition owns its own index partition.

Advantages

✔ Easier maintenance

✔ Faster partition operations

✔ Preferred with partitioned tables

Global Index

Single index across all partitions.

Advantages

✔ Better for queries across multiple partitions

Disadvantages

• Requires maintenance after partition operations

7. Index Monitoring

Enable monitoring

ALTER INDEX IDX_EMP

MONITORING USAGE;

Check usage

SELECT *

FROM V$OBJECT_USAGE;

Useful Views

• DBA_INDEXES
• DBA_IND_COLUMNS
• DBA_IND_STATISTICS
• DBA_OBJECTS
• V$OBJECT_USAGE

8. Index Rebuild vs Coalesce

Rebuild

ALTER INDEX IDX_EMP REBUILD;

Use When

• Heavy fragmentation
• Large deleted entries
• Storage reorganization

Coalesce

ALTER INDEX IDX_EMP COALESCE;

Use When

• Light fragmentation
• Online maintenance
• Low resource usage

9. Common Mistakes

❌ Creating indexes on every column

❌ Too many indexes

❌ Bitmap indexes in OLTP

❌ Ignoring unused indexes

❌ Function in WHERE clause without Function-Based Index

❌ Rebuilding indexes unnecessarily

❌ Using Global Indexes without understanding maintenance

Best Practices

✔ Create indexes based on workload

✔ Review Execution Plans before adding indexes

✔ Monitor index usage regularly

✔ Remove unused indexes

✔ Gather optimizer statistics

✔ Prefer Local Indexes on partitioned tables

✔ Avoid over-indexing

✔ Schedule index maintenance

Useful Data Dictionary Views

SELECT * FROM DBA_INDEXES;

SELECT * FROM DBA_IND_COLUMNS;

SELECT * FROM DBA_IND_EXPRESSIONS;

SELECT * FROM DBA_IND_STATISTICS;

SELECT * FROM V$OBJECT_USAGE;

Quick Comparison

Index Type	Best For	Avoid
B-Tree	OLTP, PK, FK	Very small tables
Bitmap	Reporting	Heavy DML
Function-Based	Function predicates	Unused functions
Reverse Key	RAC, sequential inserts	Range scans
Invisible	Testing	Permanent production use
Local	Partitioned tables	Cross-partition queries
Global	Multi-partition queries	Frequent partition maintenance

Interview Questions

Why does Oracle choose a Full Table Scan even though an index exists?

Because the optimizer determines that a Full Table Scan is cheaper based on statistics, selectivity, or the amount of data being returned.

Difference between Rebuild and Coalesce?

Rebuild recreates the index completely, whereas Coalesce merges fragmented leaf blocks online with fewer resources.

When should Bitmap indexes be used?

Bitmap indexes are ideal for read-heavy Data Warehouse environments with low-cardinality columns and infrequent DML.

Conclusion

Oracle Indexes are the foundation of SQL performance tuning. Selecting the right index type, monitoring usage, and following maintenance best practices can dramatically improve application performance while reducing database overhead.

Understanding B-Tree, Bitmap, Function-Based, Reverse Key, Invisible, Local, and Global indexes is an essential skill for every Oracle DBA and Database Developer.