15.00 - Table Access - Teradata Database

Teradata Database SQL Fundamentals

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

Table Access

Teradata Database uses indexes and partitions to access the rows of a table. If indexed or partitioned access is not suitable for a query, or if a query accesses a NoPI table that does not have an index defined on it, the result is a full-table scan.

Access Methods

The following table access methods are available to the Optimizer:

 

Effects of Conditions in WHERE Clause

For a query on a table that has an index defined on it, the predicates or conditions that appear in the WHERE clause of the query determine whether the system can use row hashing, or do a table scan with partition elimination, or whether it must do a full-table scan.

The following functions are applied to rows identified by the WHERE clause, and have no effect on the selection of rows from the base table:

 

Statements that specify any of the following WHERE clause conditions result in full-table scans (FTS). If the table has a PPI, partition elimination might reduce the FTS access to only the affected partitions.

 

The type of table access that the system uses when statements specify any of the following WHERE clause conditions depends on whether the column or columns are indexed, the type of index, and its selectivity:

  • column_name = value or constant expression
  • column_name IS NULL
  • column_name IN (explicit list of values)
  • column_name IN (subquery)
  • condition_1 AND condition_2
  • different data types
  • table1.column_x = table2.column_x
  • In summary, a query influences processing choices:

  • A full-table scan (possibly with partition elimination if the table has a PPI) is required if the query includes an implicit range of values, such as in the following WHERE examples.
  • When a small BETWEEN range is specified, the Optimizer can use row hashing rather than a full-table scan.

    ... WHERE column_name [BETWEEN <, >, <>, <=, >=]
    ... WHERE column_name [NOT] IN (SELECT...)
    ... WHERE column_name NOT IN (val1, val2 [,val3])
  • Row hashing can be used if the query includes an explicit value, as shown in the following WHERE examples:
  • ... WHERE column_name = val
    ... WHERE column_name IN (val1, val2, [,val3])

    Related Topics

     

    For more information on …

    See …

    the efficiency, number of AMPs used, and the number of rows accessed by all table access methods

    Database Design.

    strengths and weaknesses of table access methods

    Introduction to Teradata.

    full-table scans

    “Full-Table Scans” on page 169.