Join Index Types | Database Design | VantageCloud Lake - Join Index Types - Teradata VantageCloud Lake

Lake - Database Reference
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
ohi1683672393549.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
ohi1683672393549

Each type of join index has a unique role in enhancing the performance of your database queries without denormalizing the base tables that support your ongoing ad hoc data warehouse activities. Join index tables cannot be accessed directly by users and are not part of the logical design for a database, and therefore 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 must cover the request, and thereby be selected by the Optimizer for the query plan (unless another plan is less costly).

Any join index type can be defined with a row-partitioned primary index if the join index is not 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 One of the following:
  • A column subset of a 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 large table with a primary index defined on the same join key and to which the column subset 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 large.

  • The index can emulate vertical partitioning of the base table by selecting a small subset of the most frequently accessed columns of a wide table.
    Possibilities 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.

    This creates a mirror image of the base table that the Optimizer never uses for its query plans, but which adds useless maintenance overhead to the system.

See Single-Table Join Indexes 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 Optimizer can select the join index 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 large table, its NUPI must 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 Optimizer can select the join index 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 you to limit the rows in the index to a filtered subset of the rows in the component base table set. A common use of this feature is to restrict the population of a join index to only those rows 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).