Interview Questions   Tutorials   Discussions   Programs   

Teradata - What is Join Index in TD and How it works?

asked mar September 20, 2014 06:38 AM  

What is Join Index in TD and How it works?


1 Answers

answered By vishnoiprem   0  
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 don’t
access the Join Index table, but the Teradata does when appropriate.

Think of join indexes as aggregates or summary tables that users don’t 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
• 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
   add comment

Your answer

Join with account you already have



Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!