Should You Drop Indexes on Exadata? A Back-To-Basics Guide

22 Apr Should You Drop Indexes on Exadata? A Back-To-Basics Guide

When you migrate to Oracle Exadata, you’re stepping into an ecosystem built specifically for the Oracle Database. With technologies like Smart Scan, Storage Indexes, and Offloading at the storage layer, Oracle says full table scans are fast—so fast, some recommend dropping your indexes altogether.

But as any experienced DBA will tell you: what sounds great in theory can be a disaster in production. But first let’s dive into a few details of what the Exadata is 

Exadata isn’t just Oracle on faster servers—it’s an engineered system. A unique blend of hardware, software, and internal networks enables features like:

• iDB protocol to offload SQL commands to storage. Oracle Database, running on the Exadata Database Servers, leverages a purpose-built protocol – iDB – and Remote Direct Memory Access (RDMA) over Converged Ethernet (RoCE) to communicate with the Exadata Storage.
• Cell Servers The core Exadata System Software component responsible for the majority of the services provided by the Storage Servers, including SQL Offload, I/O Resource Management (IORM), Exadata RDMA Memory (XRMEM) and Flash Cache tiering
• Flash Cache and RDMA Memory a multi-level tiering of data into high-speed, low-latency storage media) enables staggering performance that’s visible to the database
• Smart Scan and Storage Indexes that reduce I/O dramatically

Smart Scan

Smart Scan lets Exadata process queries at the storage layer, sending back only relevant rows and columns. This works best when:

• You’re using full table scans or index fast full scans
• Queries use simple predicates (e.g., status = 'NEW')
• Data is stored in Smart Scan-capable ASM disk groups

But Smart Scan is not guaranteed. It won’t trigger if:

• You’re querying LOBs, IOTs, or clustered tables
• You use functions on columns (e.g., TO_CHAR(date))
• There’s no predicate in the query

Storage Indexes

Storage Indexes don’t point to rows—they store min/max values for columns in 1MB disk chunks. If your predicate doesn’t match any chunk’s range, Exadata skips the I/O entirely.

Advantages:

• Fully automatic, memory-based
• Great for eliminating large reads quickly
• Critical for Smart Scan to shine

Limitations:

• Only 8 columns tracked per chunk
• Not persistent after reboot (in older versions)
• Ineffective for wildcards (LIKE), !=, or sparse values

*Richard Foote’s blog post shows this perfectly: if rare values are scattered, Storage Indexes won’t eliminate any I/O. A regular index would.

Real-Life Gotcha

At a financial institution I supported, a team dropped several indexes post-Exadata migration. They assumed Smart Scan and Storage Indexes would handle everything.

Big mistake.

One quarterly report went from 2 minutes to 45 minutes. Why? A status != 'CLOSED' clause—which Storage Indexes didn’t help with.

After investigation, we restored one of the dropped B-tree indexes and performance was back instantly.

What to Do Instead of Dropping Indexes

1. Test with invisible indexes, Monitor performance with and without the index before deleting.

ALTER INDEX your_index INVISIBLE;
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

2. Test and Simulate absence of Smart Scan:

ALTER SESSION SET cell_offload_processing = FALSE;

3. Use Flash Cache for critical segments:. Pin hot indexes to flash cache, Ensures low-latency reads from flash, even if Smart Scan isn’t active

ALTER INDEX in_t2 STORAGE (CELL_FLASH_CACHE KEEP);

Conclusion

Exadata uses a different paradigm for database performance, with features like Smart Scan and Storage Indexes offering remarkable gains—when used under the right circumstances. But as I mentioned, these features are not magic bullets. In real-world workloads, not every query fits the ideal mold. Mastering Exadata isn’t about blindly following best practices. It’s about understanding the details, measuring impact, and choosing the approach that truly fits.