Join Indexes

The join index JOIN the two tables together and keeps the result set in the permanent space of Teradata. This JOIN index will hold the result set of the two table, and at the time of JOIN parsing engine will decide whether it is fast to build the result set from the actual BASE tables or the JOIN index. User never directly query the JOIN index. In the sense JOIN index is the result of joining two tables together so that parsing engine always decide to take the result set from this JOIN index instead of going and doing manual join on the base table.

Types of JOIN index –
Multi table JOIN index

Suppose we have two BASE tables EMPLOYEE_TABLE and DEP_TABLE, which holds the data of EMPLOYEE and DEPARTMENT respectively. Now a JOIN index on these two tables will be somewhat-

This way the JOIN index EMP_DEPT holds the result set of two BASE tables, and at the time of JOIN PE will decide weather it is faster to join actual tables or to take result set from this JOIN index. So always choose wise list of columns and tables to create JOIN index.

Single Table JOIN index

A single table JOIN index duplicate a single table, but changes the primary index. Users will only query the base table and its PE who decide which result set is faster, from JOIN index or from actual BASE tables. The reason to create the single table JOIN index is so joins can be performed faster because no redistribution or duplication needs to occur.

Aggregate JOIN index

An aggregate JOIN index will allow the tracking of Averages SUM and COUNT on any table. This JOIN index is basically used if we need to perform any aggregate function in the data of the table.

The main fundamentals of JOIN indexes are –

• JOIN index is not a pointer to data it actually store data in PERM space
• Users never query them directly, its PE who decide which result set to take
• Updated when base tables are changed
• Can’t be loaded with Fastload or Multiload.