Other functionally similar strategies for solving this problem can also be used. Only prototyping can determine which among the possible choices is best for an application environment and hardware configuration.
- The design technique of assigning a NUPI to a subentity table that hashes related rows to the same AMPs as a related major entity is superficially similar to a single-table join index. The differences are as follows.
- Cardinalities
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 and 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.
- Specialization
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.
- Cardinalities
- You can create a multitable join index that prejoins the entity attributes most likely to be joined in a query.
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, the 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.
- You can create a denormalized prejoin base table.
Denormalization reduces the generality of the database for ad hoc queries and data mining operations and introducing 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 Deciding between Single-Table Join Index and Ordinary Join Index for an example of using a single-table join index.