A join index (JI) can facilitate queries that involve joins, aggregations, or access a commonly referenced subset of the data in a table.
- A join index can be created to pre-join a set of tables.
- A join indexes can be created with aggregation.
- A join index itself can have a PI (or PA if column partitioned), and then the join index rows are distributed to AMPs based on the hashed values of their primary index columns.
- A join index can be partitioned by row, by column, or by a combination of both.
- In addition to limiting the columns of a join index to frequently-joined columns, join indexes can be created using a WHERE clause to limit the rows in the index. If the Optimizer can use the join index in query processing, the query will execute faster if the index has fewer rows than the base tables.
A multitable join index pre-joins rows from frequently-joined tables. When queries involve joins that match such a JI, the Teradata Optimizer can plan the query using the pre-joined JI rather than the base tables, speeding up query performance.
A single-table join index only references one table. You can create a single-table join index with the same PI or PA definition as another table to which the indexed table is frequently-joined. The join index table rows are then distributed to the same AMPs as the rows of the other table. Consequently, joins between rows of the join index and rows of the joined table are performed locally on each AMP. These joins would not require any row duplication or redistribution. Also, often sorting the rows can be avoided. Thus, join processing is faster. In some situations, a single-table join index can provide better database performance than a multitable join index because the single-table join index requires less maintenance overhead when data changes in the joined tables.
For example, if two frequently joined tables share a PK-FK relationship, a single-table join index can be created for one of the tables, specifying the join index PI as the key columns of that relationship. This will distribute the rows of the join index to the same AMPs as those of the related table, speeding up queries that join the two tables. The single-table join index itself need only be updated when one of the two tables changes.
A single-table join index can also be used to facilitate access to the data of a table by being a commonly referenced subset or aggregation of the data, having a different PI or PA, or a different partitioning scheme. This allows the base table and join index to meet the needs of different types of queries. Note that, for this specific usage of a join index, there is actually no join involved. It is possible that this same join index could be used in other queries to benefit join processing.