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.