Data Access Path - Full Table Scan

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > RDBMS - Sql Processing (Engine)

1 - About

This type of scan reads all rows from a table and filters out those that do not meet the selection criteria.

Advertising

3 - Why a Full Table Scan Is Faster for Accessing Large Amounts of Data

Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. This is because full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls.

4 - When the Optimizer Uses Full Table Scans

The optimizer uses a full table scan in any of the following cases:

  • Lack of Index

If the query is unable to use any existing indexes, then it uses a full table scan. For example, if there is a function used on the indexed column in the query, the optimizer is unable to use the index and instead uses a full table scan. If you need to use the index for case-independent searches, then either do not permit mixed-case data in the search columns or create a function-based index, such as UPPER(last_name), on the search column. See “Using Function-based Indexes for Performance”.

  • Large Amount of Data

If the optimizer thinks that the query will access most of the blocks in the table, then it uses a full table scan, even though indexes might be available.

  • Small Table

If a table contains data that can be read in a single I/O call, then a full table scan might be cheaper than an index range scan, regardless of the fraction of tables being accessed or indexes

  • High Degree of Parallelism

A high degree of parallelism for a table skews the optimizer toward full table scans over range scans.

When a full table scan is required, response time can be improved by using multiple parallel execution servers for scanning the table. Parallel queries are used generally in low-concurrency data warehousing environments, because of the potential resource usage.