Vantage 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.
- 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.
- GROUP BY
- ORDER BY
- WITH ... BY
- 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
- 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
- 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])
- The efficiency, number of AMPs used, and the number of rows accessed by all table access methods, see Teradata Vantage™ - Database Design, B035-1094.
- Strengths and weaknesses of table access methods, see Teradata Vantage™ - Database Introduction, B035-1091.
- Full-table scans, see Full-Table Scans.