16.10 - Full-Table Scans - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)

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 Database Design, B035-1094.
  • Cylinder reads, see Database Administration , B035-1093 .
  • Enabling data block read-ahead operations, see DBS Control Utility in Utilities , B035-1102 .