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.
The following table access methods are available to the Optimizer:
- Unique Primary Index
- Unique Partitioned Primary Index
- Nonunique Primary Index
- Nonunique Partitioned Primary Index
- Unique Secondary Index
- Nonunique Secondary Index
- Join Index
- Hash Index
- Full-Table Scan
- Partition Scan
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:
- GROUP BY
- ORDER BY
- WITH ... BY
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.
- nonequality comparisons
- column_name IS NOT NULL
- column_name NOT IN (explicit list of values)
- column_name NOT IN (subquery)
- column_name BETWEEN ... AND ...
- condition_1 OR condition_2
- NOT condition_1
- column_name LIKE
- column_1 || column_2 = value
- table1.column_x = table1.column_y
- table1.column_x [computation] = value
- table1.column_x [computation] - table1.column_y
- INDEX (column_name)
- SUBSTR (column_name)
- missing WHERE clause
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])
For more information about:
- The efficiency, number of AMPs used, and the number of rows accessed by all table access methods, see Database Design, B035-1094.
- Strengths and weaknesses of table access methods, see Introduction to Teradata.
- Full-table scans, see “Full-Table Scans.”