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-CREATE JOIN INDEX EMP_DEPT AS SELECT EMP_NO,EMP_NAME, EMP_DEPT, EMP_SAL, EMP_MGR FROM EMPLOYEE_TABLE EMP INNER JOIN DEP_TABLE DEP ON EMP.EMP_DEPT = DEP.DEPT_NO UNIQUE PRIMARY INDEX (EMP_NO);
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.CREATE JOIN INDEX EMP_SNAP AS SELECT EMP_NO, EMP_NAME, EMO_DEPT FROM EMPLOYEE_TABLE PRIMARY INDEX(EMP_DEPT);
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.CREATE JOIN INDEX AGG_TABLE SEL DEPT, AVG(EMP_SAL) FROM EMP_SALARY GROUP BY 1;
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.