The orders dashboard is slow, so you pull up the Oracle execution plan. There it is: TABLE ACCESS FULL on a million-row table. The query filters on status, and there’s already an index on that column. You run the same query with a different status and see an index range scan. It’s tempting to conclude that Oracle missed an obvious optimization on the first run.
Now look at what the two queries ask for. One totals nearly every order the system has ever shipped. The other totals a small set of orders held for review. Both have the same SQL shape and use the same filter column, but they ask Oracle to retrieve very different amounts of data. We’ll follow both access paths, see where the work goes, and use the executed plan to check whether the full scan makes sense.
An index finds the rows. Oracle may still need the table.
An access path is how the database retrieves rows. With a B-tree index on status, Oracle can navigate to the first matching entry and walk the matching leaf entries.…