Join Index Types | Database Design | Teradata Vantage - Join Index Types - Advanced SQL Engine - Teradata Database

• 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 for additional information.

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.

• 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.

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.

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.