A full-table scan is a retrieval mechanism that touches all rows in a table.
- 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
- 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
- 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 .