A Join Index can have repeating values and Join Indexes are automatically updated when the base tables change. They actually create a new physical table. Users dont access the Join Index table, but the Teradata does when appropriate.
Think of join indexes as aggregates or summary tables that users dont have to maintain because Teradata automatically manages the entire process. In fact, a user cannot view the row contents of join indexes even if they wanted to. Their operation is entirely transparent to the user. After deleting a base table row, you will not have to update the aggregate or joined table say goodbye to those pesky temporary tables that need manual refreshing on a daily basis or whenever the contributing table rows were changed.
Three basic types of Join Indexes
Single Table Join Index Distributes the rows of a single table on a foreign key hash value.
Multi-Table Join Index Pre-Joins multiple tables and stores and maintains the results with the base tables.
Aggregate Join Index Aggregates one or more columns into a summary table and maintains the results with the base tables.
Join Index Fundamentals
Join index implementation must be thought out thoroughly. Simply throwing a solution at a problem without carefully weighing its costs and rewards is an invitation for trouble. While join indexes are truly useful, they do not enhance performance in every situation. It would be inefficient to create join indexes for 90% of all feasible joins like denormalization, such a proposal would require exponential amounts of storage. Space consumption poses one of the most important concerns when using Join Indexes. Although not directly available through a query, Teradata must still store every row of a Join Index on disk. This is done much like any table row is stored hashed to an AMP. When a Join Index is defined you are looking at twice the amount of space needed per column. If the system is running low on physical disk storage, Join Indexes may do more harm than good.
Join Indexes also require a certain amount of overhead and upkeep. Teradata transparently maintains all join indexes so that the index rows are updated when the base rows change. This is beneficial from a human maintenance perspective because it limits the occurrence of update anomalies. However, the system overhead involved in this automatic maintenance is an important consideration. When to use a Join Index The benefits and drawbacks of a Join index are discussed in detail above. You should consider using a join index in the following circumstances: Specific, large tables are frequently and consistently joined in which the result set includes a large number of joins from joined tables. A table is consistently joined to other tables on a column other than its Primary Index. Queries all request a small, consistent subset of columns from joined tables containing many columns. The retrieval benefits are greater than the cost of setting up, maintaining and storing the join index