Last Updated: June 2026Tested On: Oracle 19c Your AWR shows INDEX FAST FULL SCAN on a query that runs every 5 minutes. The index exists. The query looks fine. But something feels off — why is Oracle reading the entire index instead of doing a range scan? Sound familiar? This post explains exactly what Oracle Read more about Oracle Index Fast Full Scan: When the Optimizer Reads the Entire Index[…]
A production AWR report flagged a query with an INDEX FULL SCAN operation consuming significant I/O. The developer assumed it was an index range scan gone wrong. It was not. The optimizer had a reason — and understanding that reason is what separates reactive troubleshooting from deliberate tuning. Tested on: Oracle Database 19c (19.28) · Read more about Oracle Index Full Scan: When and Why It Fires[…]
Distinct is the keyword that oracle used to filter out unique values from a column. However, it is very interesting to see the behavior followed by the optimizer for its access path. How Oracle Index Fast Full Scan Handles DISTINCT Queries? In this “Optimizer Access Paths” blog, we will see how we can make optimizer Read more about Optimizer Access Paths: How Index FFS works with Distinct[…]
Oracle optimizer access paths determine whether your query runs in 18 seconds or 18 milliseconds. Every SQL statement hitting your Oracle database goes through the optimizer, which silently picks between a full table scan, index range scan, or index unique scan — and that single decision defines your query’s cost. What Is an Access Path? Read more about Why Is Oracle Doing a Full Table Scan Instead of the Index?[…]