16.20 - Full-Table Scans - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Fundamentals

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
English (United States)
Last Update

A full-table scan is a retrieval mechanism that touches all rows in a table.

Teradata Database always uses a full-table scan to access the data of a table if a query:

  • Accesses a NoPI table that does not have an index defined on it
  • Does not specify a WHERE clause

Even when results are qualified using a WHERE clause, indexed or partitioned access may not be suitable for a query, and a full-table scan may result.

A full-table scan is always an all-AMP operation, and should be avoided when possible. Full-table scans may generate spool files that can have as many rows as the base table.

Full-table scans are not something to fear, however. The architecture that Teradata Database uses makes a full-table scan an efficient procedure, and optimization is scalable based on the number of AMPs defined for the system. The sorts of unplanned, ad hoc queries that characterize the data warehouse process, and that often are not supported by indexes, perform very effectively for Teradata Database using full-table scans.

Accessing Rows in a Full-Table Scan

Because full-table scans necessarily touch every row on every AMP, they do not use the following mechanisms for locating rows:

  • Hashing algorithm and hash map
  • Primary indexes
  • Secondary indexes or their subtables
  • Partitioning

Instead, a full-table scan uses the Master Index and Cylinder Index file system tables to locate each data block. Each row within a data block is located by a forward scan.

Because rows from different tables are never mixed within the same data block and because rows never span blocks, an AMP can scan up to 128K bytes of the table on each block read, making a full-table scan a very efficient operation. Data block read-ahead and cylinder reads can also increase efficiency.

Related Topics

For more information about:

  • Full-table scans, see Teradata Vantage™ - Database Design, B035-1094.
  • Cylinder reads, see Teradata Vantage™ - Database Administration, B035-1093.
  • Enabling data block read-ahead operations, see DBS Control Utility in Teradata Vantage™ - Database Utilities , B035-1102 .