Predicates That Invoke Full-Table Scans - Teradata Database

Predicates That Invoke Full‑Table Scans

The following search conditions typically invoke a full‑table scan of the base table or of a secondary index subtable when used as a HAVING or WHERE clause condition because they do not specify a specific value or set of values.

nonequality comparisons column_name IS NOT NULL column_name NOT IN (subquery) column_name NOT IN (constant_list) column_name BETWEEN value_1 AND value_2 join_condition_1 OR join_condition_2 NOT (condition) table_1.column_x * expression = value table_1.column_x * expression = table_1.column_y column_1 || column_2 = value column_name LIKE expression

INDEX (column_name) SUBSTRING (column_name) SUM (numeric_expression) MINIMUM (numeric_expression) MAXIMUM (numeric_expression) AVERAGE (numeric_expression) COUNT (value_expression) DISTINCT ANY ALL no WHERE clause specified for query

Some Teradata Database indexes can optionally be stored in the order of their values as well as in the more standard hash order. If a value-ordered index is defined on the column that satisfies a range predicate from this list, then a full‑table scan is not necessary to satisfy the condition and the Optimizer merely scans a subset of the value-ordered index subtable instead. Furthermore, for tables and join indexes defined with a partitioned primary index, row partition elimination can limit a full‑table scan to just those partitions that are not eliminated.