16.20 - Similarities and Differences Between Join Indexes and Base Tables - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)

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 Teradata Database 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 Teradata Database for details).