Join Index provides better performance by providing an alternate access path.

Teradata Join Index

Join Index provides better performance by providing an alternate access path

Join Index(JI) characteristics:
Join Index drawbacks:
When to use a Join Index:

Below points briefly describe how Join Index is different from Hash Index, visit Hash Index Vs Join Index to get more detail.
There are 3 basic types of Join Indexes


Cover Query: When all columns in a query can be satisfied by a join index table it is called a COVER QUERY!



Sparse Index: A Sparse Index is a join index with a 'WHERE' Clause. Sparse Index(Join Index) table are smaller since data is filtered with 'WHERE' clause and as a result.

Global Join Index: Global join index is a type of join index where ROWID of base table/tables is added in the column list. Teradata internally uses these RowIds to found other columns (not listed in Join Index) quickly.



Value Ordered Join Index: By default, join index subtable rows are sorted locally on each AMP by the row hash value of the NUPI column,it is possible to store rows in value order.