Table scan

Table scan

A table scan is the easiest and simplest operation that can be performed against a table. It sequentially processes all of the rows in the table to determine if they satisfy the selection criteria specified in the query. It does this in a way to maximize the I/O throughput for the table.
A table scan operation requests large I/Os to bring as many rows as possible into main memory for processing. It also asynchronously pre-fetches the data to make sure that the table scan operation is never waiting for rows to be paged into memory. Table scan however, has a disadvantage in it has to process all of the rows in order to satisfy the query. The scan operation itself is very efficient if it does not need to perform the I/O synchronously.
Table 1. Table scan attributes
Data access method Table scan
Description Reads all of the rows from the table and applies the selection criteria to each of the rows within the table. The rows in the table are processed in no guaranteed order, but typically they are processed sequentially.
Advantages

Minimizes page I/O operations through asynchronous pre-fetching of the rows since the pages are scanned sequentially

Requests a larger I/O to fetch the data efficiently

Considerations

All rows in the table are examined regardless of the selectivity of the query

Rows marked as deleted are still paged into memory even though none will be selected. You can reorganize the table to remove deleted rows.

Likely to be used

When expecting a large number of rows returned from the table

When the number of large I/Os needed to scan is fewer than the number of small I/Os required to probe the table
Example SQL statement
SELECT * FROM Employee WHERE WorkDept BETWEEN 'A01'AND 'E01'
OPTIMIZE FOR ALL ROWS
Messages indicating use
Optimizer Debug:
CPI4329 — Arrival sequence was used for file EMPLOYEE 
PRTSQLINF:
SQL4010 — Table scan access for table 1.
SMP parallel enabled Yes
Also referred to as Table Scan, Preload
Visual Explain icon
Parent topic:
Table

Related concepts

Nested loop join implementation

Data access method	Table scan
Description	Reads all of the rows from the table and applies the selection criteria to each of the rows within the table. The rows in the table are processed in no guaranteed order, but typically they are processed sequentially.
Advantages	Minimizes page I/O operations through asynchronous pre-fetching of the rows since the pages are scanned sequentially Requests a larger I/O to fetch the data efficiently
Considerations	All rows in the table are examined regardless of the selectivity of the query Rows marked as deleted are still paged into memory even though none will be selected. You can reorganize the table to remove deleted rows.
Likely to be used	When expecting a large number of rows returned from the table When the number of large I/Os needed to scan is fewer than the number of small I/Os required to probe the table
Example SQL statement	SELECT * FROM Employee WHERE WorkDept BETWEEN 'A01'AND 'E01' OPTIMIZE FOR ALL ROWS
Messages indicating use	Optimizer Debug: CPI4329 — Arrival sequence was used for file EMPLOYEE PRTSQLINF: SQL4010 — Table scan access for table 1.
SMP parallel enabled	Yes
Also referred to as	Table Scan, Preload
Visual Explain icon