15.00 - Full-Table Scans - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Full-Table Scans

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 on …

    See …

    full-table scans

    Database Design.

    cylinder reads

    Database Administration.

    enabling data block read-ahead operations

    DBS Control Utility in Utilities: Volume 1 (A-K).