Index Type Comparisons | Database Design | Teradata Vantage - Index Type Comparisons - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
qby1588121512748.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantage™

The following table summarizes the similarities and differences among primary, secondary, join, and hash indexes.

Attribute Primary Index or Primary AMP Index Secondary Index Join Index
Required No. If a PI, PA, or NoPI is not explicitly defined, Teradata Database chooses a default PI or NoPI based on other clauses specified and a DBS Control setting. No No.
Maximum per table 1 32 for the combined number of secondary and join indexes, including any system defined indexes, which are used to implement PK and UNIQUE constraints.

Each multicolumn NUSI that specifies an ORDER BY clause counts as two consecutive indexes in this calculation.

Maximum number of columns 64 64 64 per referenced base table.

No more than 128 columns can be defined for a row compressed join index, 64 each for the fixed and variable parts.

Unique index supported PI: Yes

PA: No

Yes Yes
Nonunique index supported Yes Yes Yes
Index stored separately, requiring maintenance when base table is updated. No Yes (as a subtable) Yes (as an internal table)
Partitioning allowed Yes (with some restrictions) No Yes (with some restrictions)
Value ordering allowed No Yes for NUSI on a 4-byte or less integer, DECIMAL, and DATE column only. Yes for a 4-byte or less integer, DECIMAL, and DATE column only (with some restrictions).
Hash ordering Yes for PI. Hash ordering is on the index columns.

No for PA or NoPI.

Yes for NUSI. Hash ordering is on the index columns or specified columns.

Yes for USI. Hash ordering is on the index columns.

Yes for a join index with a PI. Hash ordering is on the PI columns.

No for a join index with a PA or NoPI.

Data Access Method Relative Efficiency
UPI Highly efficient if specific index values are specified in query.
NUPI Very efficient if specific index values are specified in query, index selectivity is high, and skew is low. For queries of row-partitioned tables, performance degrades as a function of the number of row partitions that must be accessed.
PA Efficient if specific index values are specified in query and more efficient if PA is based on one column partition that is stored and compressed in COLUMN format.
USI Very efficient if index values are specified in query.
NUSI Efficient if index values are specified in query, the number of rows accessed is relatively small compared to the number of rows in the table. Also, A NUSI may be efficient for queries with range conditions or specific values for a subset of the index columns.
JI Very efficient when index is applicable.
Full-table Scan Efficient as all AMPs scan their rows in parallel to satisfy a query, without the overhead of additional indexes, or when an index is not applicable.
Data Access Method AMPs Accessed Rows Returned Query Spool Space Required?
UPI 1 0 or 1 No
NUPI 1 0 or more If a query returns a single response, the response is directly returned and no spool is required, otherwise a spool is required.
PA 1 0 or more Yes
USI 2 typically. If the base table row and the USI subtable row hash to the same AMP, then only 1 AMP is accessed. 0 or 1 No
NUSI 1 if the NUSI is defined on the same column set as table’s PI or PA otherwise, all AMPs in the table’s map. 0 or more Yes
JI 1 if the join index is accessed by its PI or PA, otherwise, all AMPs in the JI’s map. Also, additional AMPs may need to be accessed in order to access referenced based table rows. 0 or 1 if the join index is accessed by its UPI, otherwise 0 or more. If a query returns a single response, the response is directly returned and no spool is required, otherwise a spool is required.
Full-table Scan All AMPs in the table’s map. 0 or more Yes