15.00 - Single-Table Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Single‑Table Join Indexes

You can define a simple join index on a single table. A single-table join index is a database object created using the CREATE JOIN INDEX statement, but specifying only one table in its FROM clause. This permits you to hash some or all of the columns of a large replicated base table on a foreign key that hashes rows to the same AMP as another large table. In some situations, this is more high-performing than building a multitable join index on the same columns. In effect, you are redistributing an entire base table or a frequently accessed subset of base table columns using a join index when you do this. The main advantage comes from less under-the-covers update maintenance on the single‑table form of the index.

Single‑table join indexes are the only type of join index that can be defined with a unique primary index.

The term single-table join index might seem to be a contradiction because there are no joins in a single‑table join index. However, the Optimizer can use single‑table join indexes to facilitate joins. The single‑table join index came about because an observant software architect had the insight that it was possible to use the join index mechanism with a single table to horizontally partition all or a subset of a very large base table as a join index on a different primary index than that used by the original base table in order to hash its rows to the same AMPs as another very large base table that with which it was frequently joined. In this respect, a single‑table join index is essentially a hashed NUSI.

Because of the way the rows of a column‑partitioned join index are distributed to the AMPs, this advantage does not generalize to them (see “Row Allocation for Teradata Parallel Data Pump” on page 237) or by the Fast Load utility (see “Row Allocation for FastLoad Operations Into Nonpartitioned NoPI Tables” on page 238). However, column‑partitioned join indexes are useful as an alternative method to partition a base table in an entirely different way when such a option provides an appropriate choice for the Optimizer to consider for some queries.

This application is analogous to how NUPIs are often used in database design to hash the base table rows of a minor entity to the same AMP as rows from another table they are likely to be joined with in a well known query workload (see “Nonunique Primary Indexes” on page 265), though you cannot explicitly specify a join to a join index in a DML request. Instead, the Optimizer must determine if joining base table rows with join index rows would be less costly than other methods.