Full-Table Scans | SQL Fundamentals | Teradata Vantage - 17.10 - Full-Table Scans - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

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

The 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 used to make 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 the 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 Information

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.