Join Index Types

Teradata Database Design

Product

Teradata Database

Release Number

15.10

Language

English (United States)

Last Update

2018-10-06

Product Category

Software

You can define several types of join index, each having its unique role in enhancing the performance of your database queries without denormalizing the base tables that support your ongoing ad hoc data warehouse activities. Because join index tables cannot be accessed directly by users and are not part of the logical design for a database, they can be used to create persistent prejoin and summary tables without removing or otherwise lessening the ability of your databases to support a wide range of ad hoc queries.

You can also create views whose SELECT definitions are identical to those of a join index, then whenever a user accesses data using that view, the similarly‑defined join index should cover the request, and thereby be selected by the Optimizer for the query plan it develops (unless it discovers and chooses a plan that is less costly).

Note that any join index type can be defined with a row-partitioned primary index as long as it is not also row‑compressed.

Following is a summary of the join index types and their common uses.

Join Index Type	Description
Simple	A join index table defined without aggregation.
Single table	Several potential applications exist. A column subset of a very large base table defined with a NUPI defined on a join key that causes its rows to be hashed to the same AMP as another very large table with a primary index defined on that same join key and to which it is frequently joined. Useful for resolving joins on large tables without having to redistribute the joined rows across the AMPs. Subentities are typically small tables, while major entity tables are typically quite large. The index can emulate vertical partitioning of the base table by selecting a small subset of the most frequently accessed columns of a very wide table. Several possibilities exist for the vertical partitioning solution: The base table and the join index can have the same primary index. The base table and the join index can have different primary indexes. This option is particularly useful when frequent requests against the base table specify predicates on non-primary index columns. The index can be defined to contain all the columns defined for its base table, but with a different primary index. Another variable to consider is alternate orderings of the index: Hash ordering. Value ordering. A final valid, though pointless, option is to define the single‑table join index over all the columns of its base table and also to define its primary index on the same column set as the base table. All this does is create a mirror image of the base table that not only will never be used by the Optimizer for its query plans, but which also adds a great deal of useless maintenance overhead to the system. See “Single‑Table Join Indexes” on page 496 for additional information.
Multitable	A column subset of two or more major tables that are frequently joined defined as a prejoin of those tables. The defined prejoin, or join index, permits the Optimizer to select it to cover frequently made join queries rather than specifying that its underlying base tables be searched and joined dynamically.
Aggregate	A join index table defined with aggregation on one or more of its columns.
Single‑table aggregate	A column subset of a base table defined with additional columns that are aggregate summaries of base table columns. If the summary table is to be used simply to maintain aggregates for an overlying base table without denormalizing the database, then its NUPI need not be defined on a join key column set. If the summary table is to be used to maintain aggregates for an overlying base table and to be joined frequently with another very large table, then its NUPI should be defined on a join key column set that hashes its rows to the same AMPs as the base table primary index.
Multitable aggregate	A column subset, including aggregates defined for one or more columns, of two or more major tables that are frequently joined defined as a prejoin of those tables. The defined prejoin, or join index, permits the Optimizer to select it to cover frequently made join queries that also compute aggregates rather than specifying that its underlying base tables be searched, aggregated, and joined dynamically.
Sparse	Any join index that limits its rows to those satisfying a constant condition in the WHERE clause of its definition. A sparse join index permits the database designer to limit the rows in the index to a tightly filtered subset of the rows in the component base table set. A common use of this feature might be to restrict the population of a join index to only those rows that are frequently accessed by a commonly performed query or set of queries.
Column‑partitioned	Useful in support of DML requests that access a variable, selective, small subset of the columns (as specified in predicates or projection lists) and rows of a column‑partitioned table. Also useful in support of RowID joins between a column‑partitioned table and another table. Column‑partitioned join indexes have the following restrictions: Must be a single‑table join index Cannot compute aggregates Cannot be row‑compressed Cannot have value‑ordering (but can have row partitioning).