The following table summarizes the similarities and differences among Teradata Database 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
No more than 128 columns can be defined for a row compressed join index, 64 each for the fixed and variableparts.
|Unique index supported||PI: 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 ot 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 requierd, otherwise a spool is required.|
|Full-Table Scan||All AMPs in the table’s map.||0 or more||Yes|