A Join Index (JI) is an indexing structure containing columns from one or more base tables.
Some queries can be satisfied by examining only the JI when all referenced columns are stored in the index. Such queries are said to be covered by the JI. Other queries may use the JI to qualify a few rows, then refer to the base tables to obtain requested columns that are not stored in the JI. Such queries are said to be partially-covered by the index.
Because Teradata Database supports multitable, partially-covering JIs, all types of JIs, except the aggregate JI, can be joined to their base tables to retrieve columns that are referenced by a query but are not stored in the JI. Aggregate JIs can be defined for commonly-used aggregation queries.
Much like SIs, JIs impose additional processing on insert and delete operations and update operations which change the value of columns stored in the JI. The performance trade-off considerations are similar to those for SIs.
Single-table Join Indexes
Join indexes are similar to base tables in that they support a primary index, which can be used for direct access to one or a few rows.
A single-table JI is a an index structure that contains rows from only a single-table. This type of structure has been found to be very useful by Teradata Database users because it provides an alternative approach (primary index) to directly accessing data.
Multitable Join Indexes
When queries frequently request a particular join, it may be beneficial to predefine the join with a multitable JI. The Optimizer can use the predefined join instead of performing the same join repetitively.
Aggregate Join Indexes
Aggregate operations calculate a single value from individual column values in several rows of a table. Sums and averages calculated from sets of column values are examples of aggregate operations. If the same aggregate operation is frequently performed on the same columns, an aggregate JI can provide improved query performance because Teradata Database can use the aggregate index to satisfy queries, rather than repeating the aggregate calculations for every query. You can define aggregate JIs on one or more tables.
Sparse Join Indexes
Indexes include a subset of the columns of one or more tables. Typically, indexes include the column values from all rows in the table. Sparse join indexes further limit the index to include only a subset of the table rows, in addition to a subset of table columns. Sparse JIs can include one or more tables, and can also be aggregate JIs.
If queries frequently are limited to a subset of the rows of a table, for example, rows with a specific value in one column, a sparse JI that includes only those rows can improve performance by providing a more limited data set to be queried.