Join Index in Teradata

A join index is an indexing structure containing columns from multiple tables, specifically the resulting columns from one or more tables. Rather than having to join an individual table each time the join operation is needed, the query can be resolved via a join index and, in most cases, dramatically improve performance.

• It is used to define a pre-join table on frequently join column.
• You can define Join Index in Teradata on Single or multiple tables.
• Can be defined to create a partial or full replication of base table with a different primary index.
• Join Index is stored in the permanent space and maintained by the system automatically.
• You cannot fire any query on the join index directly.

Keep in mind that the defining join index in Teradata does not imply that parsing engine will use join index always. It is up to Teradata parsing engine whether to use join index or access from the base table. There are different types of join index in Teradata:-  

Single Table Join Index(STJI):-

A single table join index is used to create indexing structure for a single table but with different primary index. This improves the performance of joins as no distribution or duplication is needed. The user will query on the base table, but PE will decide whether to access base table or single table join index.

Single Table Join Index syntax:

CREATE  JOIN INDEX STUDENT_IX
AS
SELECT  ROLL_NO,STUDENT_NAME, DEPT_NO, PH_NO
FROM STUDENT_BASE_TABLE
PRIMARY INDEX(DEPT_NO);

Here DEPT_NO column defined as primary index of STJI. So during join processing with DEPARTMENT table on DEPT_NO, there is no need to re-distribute STUDENT_BASE_TABLE table using DEPT_NO column as we already defined STUDENT_IX with the primary as of DEPT_NO.

Multi Table Join Index(MTJI):-

A multi table join index is used to hold pre-join result set from the two or more columns. So during join processing PE may decide to access data from the MTJI rather than joining again underlying base tables. We need to remember that we should define MTJI after lots of analysis based on frequency and cost of joining.

Multi Table Join Index syntax:

CREATE JOIN INDEX STUDENT_DEPT
AS
SELECT ROLL_NO, STUDENT_NAME ,DEPT_NO, ADDRESS, PH_NO
FROM STUDENT_BASE_TABLE A 
INNER JOIN
ON A. DEPT_NO = B. DEPT_NO
UNIQUE  PRIMARY INDEX(ROLL_NO);

Aggregate Join Index:-

Aggregate join indexes offer an extremely efficient  method of resolving queries that frequently specify the same aggregate operations on the same column or columns. When aggregate join indexes are available, the system does not have to repeat aggregate calculations for every query. An aggregate join index can be defined on two or more tables, or on a single table.

An aggregate join index can be created using:

• SUM function
• COUNT function
• GROUP BY clause

Aggregate Join Index example:

CREATE JOIN INDEX SALES_INX STORE_NO, SUM(QTY_SOLD) 
FROM SALES_BASE_TABLE 
GROUP BY 1, ORDER BY 2;

Sparse Join Index:-

Sparse Join index a portion of the table using WHERE clause predicates to limit the rows indexed. When base tables are large, this feature can be used to reduce the content of the join index to only the portion of the table that is frequently used if the typical query only references a portion of the rows. This capability:

• Reduce the space requirement for a Join Index.
• Reduces cost to maintain join index.

Sparse Join Index syntax:

CREATE JOIN INDEX SPARSE_BILL_INX
AS
SEL ACC_NO, CUST_NAME, BILL_DATE, BILL_AMT
FROM BILL_DETAILS
WHERE BILL_YEAR=2015
UNIQUE PRIMARY INDEX(ACC_NO);

Limitation of Join Index:-

• The Join indexes are not supported by Fastload and Multiload utilities, They must be dropped and recreated after the table is loaded.
• During restore of a base table or database join index is marked as an invalid.
• Max 64 columns per table per Join Index.
• Join Index Subtables cannot be fall back protected.