15.00 - Selecting the Primary Index for Join Indexes - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Selecting the Primary Index for Join Indexes

As with any other Teradata Database table, you must define a primary index for any join index you create unless it is column‑partitioned. While the primary index for any join index must be defined as a NUPI, the column on which it is defined need not be non-unique; in fact, the usual preferences for defining a unique rather than a non-unique column set as the primary index for a base table also apply to join indexes.

Because join indexes are not part of the logical model for a database, and because they are de facto denormalized database objects, there might not be an overriding reason to define one column set over another as the primary index apart from how much more evenly one set might distribute join index table rows than another.

It is usually important to use the join key as the primary index for single-table join indexes, but otherwise there is no compelling theoretical reason to select one unique (or, if not unique, highly singular) column set over another as the primary index. If you create a join index to support a row-partitioned base table, you should consider creating it using row partitioning. This is valid only if the join index is not also row-compressed. You can also create a row-partitioned join index for a base table that does not have row partitioning. It is always possible that such a join index might provide an alternative organization of the data that facilitates access based on partitions. For example, suppose you have an nonpartitioned base table designed to handle efficient joins on its primary index. You might also want to create a row-partitioned join index on the table that optimizes fast row‑partition‑based access to the data. The rules for creating a row-partitioned join index are generally the same as those for creating a row-partitioned base table.

If a join index is designed to support range queries, you should consider specifying a row‑partitioned primary index for it. Note that you cannot specify a row-partitioned primary index for a row-compressed join index.

Join index primary indexes are defined analogously to base table primary indexes: with the CREATE JOIN INDEX statement.