Other functionally similar strategies for solving this problem can also be used. In general, only prototyping can determine which among the possible choices is best for a particular application environment and hardware configuration.
The following list describes some of the alternative strategies to creating single‑table join indexes:
For some applications, a hash index is a better choice than a single‑table join index if only because of its simpler syntax; however, it might be unclear what defaults Teradata Database used to create the index. In nearly all cases you can, and should, create single‑table join indexes that have identical effects on query workloads as the equivalent hash index. Also, multi-value compression can be carried over to join index but not for a hash index.
See “Hash Indexes” on page 602 for more information.
The cardinalities of tables for which a single-table join index is defined are typically very similar to the base tables they are designed to be joined with, while those for major entity‑subentity joins are typically very different, with the major entity typically having many more rows than the subentity.
The entity PI-subentity NUPI strategy is typically used when the subentity is a relatively small table in terms of its degree as well as its cardinality.
The single-table join index strategy is typically used when only a small subset of the columns from the base table from which the single-table join index is derived are frequently joined with the base table in question.
When you create a single-table join index, the parent base table from which it is derived might have a different primary index, in which case its rows hash to different AMPs. The single-table join index is a denormalized, specialized database object defined for a specific purpose, while the parent base table is a normalized, more general database object. Both tables in an entity-subentity relationship remain normalized and generalized database objects.
Updating a multitable join index can have a varying cost depending on which table in the multitable join is update, the indexes on this join index and base tables, and so on. In some cases, the update can about the same as single-table, sometimes it can be very expensive if it requires an expensive join to be able to do the maintenance.
The upside of a standard multitable join index strategy is that, at least for the queries for which they are designed, Teradata Database does not have to perform any join processing because the required rows are already prejoined. The single-table join index can avoid a costly redistribution of table rows, but join processing is still required to respond to the query.
Denormalization reduces the generality of the database for ad hoc queries and data mining operations as well as introducing various problematic update anomalies. Nevertheless, a relatively mild degree of denormalization is standard in physically implemented databases, and for some applications might be the only high-performing solution.
See “Single-Table Join Index” on page 550 for an example of using a single-table join index.