Table Access | SQL Fundamentals | Teradata Vantage - Table Access - Advanced SQL Engine

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:

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
HAVING
INTERSECT
MINUS/EXCEPT
ORDER BY
QUALIFY
SAMPLE
UNION
WITH ... BY
WITH

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)
SUM
MIN
MAX
AVG
DISTINCT
COUNT
ANY
ALL
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])
```

Table Access | SQL Fundamentals | Teradata Vantage - Table Access - Advanced SQL Engine - Teradata Database

SQL Fundamentals

Access Methods

Effects of Conditions in WHERE Clause

Related Topics