Similarities and Differences Between Join Indexes and Base Tables - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update
Product Category
Teradata Vantage™

In many respects, a join index is similar to a base table. For example, you can create NUSIs on a join index and you should collect statistics on appropriate columns and indexes of both to ensure that the Optimizer has accurate statistical summaries of their demographics.

You cannot collect statistics on a UDT column. This includes UDT columns that are components of an index.

You can also perform DROP, HELP and SHOW statements on join indexes. However, you cannot directly query or update a join index. You can do this by creating a view that has a similar definition to that of the join index, which can then be queried like any other view. Such a view provides a logical view of the data, and you can maintain it for as long as you need it. A join index, in contrast, is a physical database object that you create or delete for performance reasons. It does not affect the logical view of the data in any way.

For example, the following query is not valid because ord_cust_idx is a join index, not a base table.

SELECT o_status, o_date, o_comment
FROM ord_cust_idx;

Because join indexes are not part of the logical definition of a database, you can use them as summary and prejoin tables, both of which would otherwise violate the rules of normalization. The update anomalies presented by denormalized structures like summary and prejoin tables are avoided because Vantage implicitly performs all necessary updates to join indexes to ensure that no information is ever lost and that semantic integrity is always enforced (see Join Index Updates Are Maintained by Vantage for details).