Data Access Path - Full Table Scan

Oracle Database Sql Processing

About

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

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.

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.





Discover More
Data System Architecture
Data Warehouse - Fast Table Scan

Ways to Scan a Fact table Faster Employ Parallelism If you have the resources, then let divide the table into chunks and use all your system resources to scan it. Unfriendly in an OLTP environment,...
Card Puncher Data Processing
Oracle Database - Full Table Scans

in Oracle During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement's WHERE clause. When Oracle...



Share this page:
Follow us:
Task Runner