Teradata has a feature that is designed to increase the performance of queries. This feature is called the “join index”. Such a join index is a structure that stores the outcomes from a query. These outcomes are stored permanently and they wait for the moment when they are called.
The syntax of such a join index is straightforward:
CREATE JOIN INDEX SAN_D_FAAPOC_01.EMP_DEPT AS SELECT DEPT.DEPT_NO, DEPT.DEPT_NAME, EMP.EMP_NUM, EMP.EMP_NAME FROM SAN_D_FAAPOC_01.EMPLOYEE EMP INNER JOIN SAN_D_FAAPOC_01.DEPARTMENT DEPT ON DEPT.DEPT_NO = EMP.DEPT_NO PRIMARY INDEX(EMP_NUM);
The syntax contains the definition of a query. These outcomes are permanently stored in the database as an object that is called here as “EMP_DEPT”.
If a record gets added to one of the base tables that are used in the join index, the join index gets updated to reflect the new situation. This is the downside of the join index: each update in base table is automatically followed by an update of the join index. This involves extra overhead processing time.
Hence the creation of a join index must be weighted against the costs of a continuous update of the join index as result from changes in the base tables.
It can be decided to create a permanent table that contains the query outcomes. Such a table can be updated at fixed time intervals (in stead of an automatic update as with the join index).
Two final remark.
One. One can not address a join index directly. A statement like “select * from join_index” will only return an error code. One only uses such a join index indirectly. It is used if a query is written that looks similar to the join index. In that case, the optimiser decides that join index reduces the time to return the outcomes.
Two. One might know that a join index is used when the explain plan is read. This explain plan is show if the query is preceded by the keyword “explain”. In that case a well-written text shows how the optimiser works. One may then see whether the join index is used or not.