CSE Roadmap

Core tier · about 2 hours · 6 parts

Part 3 of 6 · 2 min read

Full scan versus index range scan

Think of a full table scan like reading every page of a book in order. An index range scan is different: flip to a bookmarked section, read only the pages near it, then check each one individually against what you actually need.

In plain words: A full table scan reads every row in the table and checks each one against the WHERE clause, so its cost grows directly with the table's size regardless of how selective the filter actually is. An index range scan instead walks only the portion of the index tree matching part of the filter, then follows the index's leaf chain to collect every matching entry, and for each one fetches the corresponding row from the table separately. A full scan reads in large sequential chunks, while an index range scan's row fetches are typically one at a time, so when the index lookup matches a large share of the table, those many individual fetches can cost more in total than one efficient sequential scan would have.

A worked example. A query filtering only on subsidiary_id, a column not covered by a useful index, produces a full table scan reading all 106 rows at a cost of 478. A different query touching an indexed column but matching many rows can look cheap at the index-scan step alone, yet still pay heavily at the row-fetch step that follows it.

The key lines.

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20;
----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT |             | 106  | 478  |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 106  | 478  |
----------------------------------------------------

Full table scan, index range scan

A full table scan reads every row in a table and evaluates the WHERE clause against each one, with cost scaling directly with table size. An index range scan walks a contiguous portion of an index matching part of the filter, then fetches each matching row from the table individually.

Example: the plan above shows a full table scan reading all 106 rows, since subsidiary_id alone is not covered by a usable index here.