Single-Table Join Indexes - Teradata VantageCloud Lake

One of the most useful constructs for tactical queries is the single-table join index. Because you can define a primary index for the join index composed of different columns than those used to define the base table primary index, you can create an alternative method of directly accessing data in the associated base table.

For example, you can create a join index on the orders table that includes a subset of the columns that a specific tactical query application may require. In the example shown in the following graphic, assume that the application has available a value for o_custkey and the clerk that placed the order, but does not have a value for o_orderkey. Specifying the primary index defined for the join index OrderJI supports direct access to order base table using the single-table join index OrderJI.

CREATE JOIN INDEX OrderJI AS
  SELECT o_custkey, o_clerk, o_totalprice, o_orderdate, o_orderkey
  FROM orders
PRIMARY INDEX(o_custkey,o_clerk);

The larger the base table, the longer a table scan takes and the greater the benefit of a join index providing single-AMP access. In this example, the join index took 8:23 to create.

A single-table join index like this one is particularly useful when the tactical application does not have the primary index of the base table available, but has an alternative row identifier. For example, when a social security number is available but a member ID, the primary index of the base table, is not. Single-AMP access is achievable using the join index if its primary index is defined on social security number.