What is a Full Table Scan (FTS) in Oracle

Full Table Scan (FTS)

During a full table scan all the formatted blocks of a table that are below High Water Mark (HWM) are scanned sequentially, and every row is examined to determine if it satisfies the query’s where clause. Since the blocks are scanned sequentially, to speed up the whole scanning process I/O read calls larger than a single block are issued. The size of these I/O read calls are determined by the DB_FILE_MULTIBLOCK_READ_COUNT parameter.

Just because during accessing large number of blocks with FTS Oracle can use multiblock I/O read call, FTS is sometimes better than index range scans.

Following are the situations where optimizer chooses Full Table Scans:-

  • Lack of Index.
  • The query predicate applies a function to the indexed column.
  • Select COUNT(*) with B-Tree indexes & Nulls.
  • Large Amount of Data
  • Small Table
  • High Degree of Parallelism
  • FTS Hint
  • The table statistics are stale.
  • The query predicate does not use the leading edge of a B-tree index..
  • The query is unselective.

Example FTS explain plan:

SQL&gt; set autot trace explain

SQL&gt; select * from dual;
Execution Plan
Plan hash value: 3543395131

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |

Feel free to comment below if you need any explanation on why a FTS would be chosen by Optimizer in any of the situations mentioned above or if you can think of any other situation where optimizer would chose FTS and we can have a discussion on the same.