Oracle Indexes: Fix Slow SQL Without Overdoing It

A SQL query running for 8 seconds may not create panic during testing. In production, the same query can become a real incident when it runs thousands of times from an application, report, or batch process.

I have seen queries drop from seconds to milliseconds after adding one correct index. I have also seen OLTP systems become slower because every column was indexed without understanding the workload. Both situations are common.

Indexes are powerful, but they are not free. They reduce unnecessary reads for SELECT queries, but they add cost to INSERT, UPDATE, DELETE, storage, statistics gathering, and maintenance. A good DBA does not create indexes blindly. A good DBA checks the SQL pattern, data volume, selectivity, execution plan, and write workload before touching production.

Why Indexes Matter in Production

When Oracle does not have a useful access path, it may scan the entire table. For a small lookup table, that may be acceptable. For a large transaction table, it can mean high logical reads, CPU pressure, slow application response, and unnecessary I/O.

The real question is not whether an index exists. The real question is whether the index matches how the application searches the data.

A query filtering by customer_id needs a different indexing approach from a report filtering by status, region, and payment_mode. Similarly, a query using UPPER(name) may not benefit from a normal index on name.

B-tree Indexes: The Normal Choice for OLTP

B-tree indexes are the default and most commonly used Oracle index type. In most OLTP systems, this is where DBAs should start.

They work well for columns with many distinct values, such as order IDs, customer IDs, email addresses, account numbers, transaction IDs, and date columns. These indexes are useful for equality searches, range scans, joins, and primary key lookups.

For example, if an application frequently searches orders by customer, a simple B-tree index can reduce unnecessary table reads.

SELECT *
FROM orders
WHERE customer_id = 10045;

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

For queries that regularly filter by multiple columns, a composite index may be better than multiple single-column indexes.

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, order_status);

Column order matters in composite indexes. If the application usually searches by customer_id first, then customer_id should usually be the leading column. Index design should follow real SQL patterns, not assumptions.

Bitmap Indexes: Useful, But Not for Every System

Bitmap indexes are useful for columns with very few distinct values, such as status, gender, active flag, region, or processed flag. They can perform very well in reporting systems and data warehouses where queries combine multiple low-cardinality filters.

CREATE BITMAP INDEX bix_orders_status
ON orders(order_status);

The problem starts when bitmap indexes are created on busy OLTP tables. Concurrent DML can suffer because bitmap locking can affect a range of rows, not just one row. If many sessions are inserting or updating at the same time, this can become a blocking issue.

My practical rule is simple: bitmap indexes belong mostly to read-heavy reporting or warehouse-style workloads. On a hot transaction table, think twice before using them.

Function-Based Indexes: When SQL Changes the Column

One common production issue is that the index exists, but Oracle still does not use it as expected. Many times, the reason is that the SQL applies a function on the column.

For example, an index on name may not help much if the application searches using UPPER(name). Oracle is not searching the raw column anymore. It is searching the result of the function.

SELECT *
FROM employees
WHERE UPPER(name) = 'JOHN';

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

Function-based indexes are useful when the application SQL cannot be changed quickly. But do not create them for every function you see. They still add storage and DML overhead.

For date columns, rewriting the predicate is often better than creating another function-based index. Instead of using TRUNC(order_date) in the WHERE clause, use a date range when possible.

WHERE order_date >= DATE '2026-07-05'
AND   order_date <  DATE '2026-07-06'

Invisible Indexes: Safer Testing Before Dropping

Invisible indexes are helpful when you want to test whether an index can be removed. The index remains in the database and continues to be maintained, but the optimizer normally ignores it.

ALTER INDEX idx_orders_status INVISIBLE;

ALTER INDEX idx_orders_status VISIBLE;

This is safer than dropping an index immediately. Some indexes are used only during month-end reports, audit jobs, quarterly reconciliation, or batch processing. A short observation window can give the wrong conclusion.

However, remember that invisible indexes are still maintained during DML. If the goal is to reduce write overhead, the benefit comes only after the index is dropped.

Checking Index Usage Without Guesswork

Before removing an index, monitor its usage and review the workload. Oracle provides index usage monitoring, but the result should be interpreted carefully.

ALTER INDEX idx_orders_status MONITORING USAGE;

SELECT index_name, used, start_monitoring FROM v$object_usage;

ALTER INDEX idx_orders_status NOMONITORING USAGE;

Do not monitor for one quiet day and drop the index. A rarely used index may still protect an important business job. The safer approach is to monitor, review SQL history, make the index invisible, observe a full business cycle, and then decide.

Oracle vs PostgreSQL Indexing View

Oracle and PostgreSQL both use B-tree indexes as the normal default, but the operational handling differs.

• Oracle supports B-tree, bitmap, function-based, invisible, domain, reverse key, compressed, and other index options.
• PostgreSQL supports B-tree, Hash, GiST, SP-GiST, GIN, and BRIN indexes.
• Oracle bitmap indexes are physical index structures. PostgreSQL may use bitmap scans during execution, but that is not the same as creating an Oracle bitmap index.
• PostgreSQL DBAs commonly use CREATE INDEX CONCURRENTLY to reduce blocking during index creation.
• Oracle DBAs may use online index creation where available and licensed.

The common lesson is the same in both databases: indexing is workload-driven. The database engine can only help if the index design matches the query pattern.

Common Failure Scenarios

1. Index Exists, But Query Still Scans the Table

This usually happens because of stale statistics, poor selectivity, function usage on the column, datatype conversion, or the optimizer deciding that a full scan is cheaper.

2. Too Many Indexes on a Hot Table

Every insert or update becomes more expensive because Oracle must maintain multiple indexes. This is painful on audit, transaction, session, queue, and event tables.

3. Bitmap Index on OLTP Table

Bitmap indexes can create blocking problems in concurrent DML workloads. They may look attractive because the column has few values, but the workload type matters more.

4. Wrong Composite Index Order

A composite index is not automatically useful. The leading column should match how the SQL filters data. Poor column order can make the index less effective.

5. Index Dropped Too Quickly

An index may not be used during normal daytime workload but may be critical during month-end or batch processing. Dropping it without a proper observation window can create a delayed production issue.

Mini Case Study: Query Slowed Down After Data Growth

A customer order search was running fine for years. Later, the same SQL started taking several seconds during peak time. The application team said the query had not changed, and they were right. The table had changed.

The table grew from a few million rows to hundreds of millions of rows. Existing single-column indexes were no longer enough for the actual search pattern. The SQL usually filtered by customer and order status together.

WHERE customer_id = :b1
AND   order_status = :b2;

CREATE INDEX idx_orders_customer_status ON orders(customer_id, order_status)
ONLINE;

After testing and refreshing statistics, logical reads dropped sharply and the query became stable again. The lesson was simple: data growth exposes weak index design. A query that behaves well at 5 million rows may become expensive at 200 million rows.

DBA Insights From Production

• Do not create indexes only because a query is slow. First check the execution plan and row selectivity.
• Review existing indexes before adding new ones. Duplicate and overlapping indexes are common in old systems.
• Be careful with bitmap indexes on transactional tables.
• For date filters, try range predicates before creating function-based indexes.
• Use invisible indexes when testing index removal.
• Monitor index usage across a real business cycle, not only during normal office hours.
• Treat indexes like production code. They need review, testing, monitoring, and cleanup.

FAQs

Why is Oracle not using my index?

Common reasons include stale statistics, poor selectivity, function usage on the column, datatype mismatch, bind variable behavior, or the optimizer estimating that a full table scan is cheaper.

Should every foreign key be indexed?

Not blindly, but many OLTP systems benefit from indexing foreign keys, especially when parent rows are deleted or updated, or when joins frequently use the foreign key column.

When should bitmap indexes be avoided?

Avoid bitmap indexes on tables with frequent concurrent inserts, updates, or deletes. They are usually better for reporting, warehouse, or read-heavy workloads.

Is a composite index better than two single-column indexes?

Sometimes yes. If the SQL always filters by two columns together, one well-designed composite index can be better than two independent indexes. The leading column is important.

How long should index usage be monitored before dropping?

Monitor long enough to include daily, weekly, month-end, batch, reporting, and audit workloads. A short monitoring window can miss rarely used but important indexes.

Conclusion

Indexes can fix slow SQL quickly, but they can also become hidden production debt. A good index reduces unnecessary reads and gives Oracle a better path to the data. A poor index adds storage, write overhead, maintenance effort, and sometimes confusion during troubleshooting.

For most OLTP workloads, B-tree indexes are the practical default. Bitmap indexes should be handled carefully and are usually better suited for read-heavy reporting systems. Function-based indexes are useful when SQL applies expressions to columns, but rewriting the predicate may be cleaner. Invisible indexes are useful when cleaning up doubtful indexes safely.

The best indexing decisions come from real workload evidence. Check the execution plan, data distribution, table growth, existing indexes, and DML volume. Do not add indexes emotionally, and do not drop them casually.

Review your top SQL regularly, clean up duplicate indexes carefully, test changes in a controlled way, and monitor the impact after every release. Index tuning is not a one-time task. It is part of healthy production database maintenance.