15.00 - Maintenance Costs of Single-Table Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Maintenance Costs of Single‑Table Join Indexes

For a single-table join index, the maintenance cost is roughly double the cost of maintaining the base table.

When you design a schema, there are often some tables that are queried in such a way that for some frequently run workloads, the table is joined on one column, but for another important query, the table is joined on another column. The usual design solution is to distribute the rows of this table on the column that is most frequently used in a join. If there is more than one column, then a join index might be a good design choice. A join index can be used to redistribute the table on the secondary join attribute so that joins can be done without a redistribution step.

Join indexes can also be used to evaluate parameterized queries. For the Optimizer to use a join index in this situation, the query must also contain a non‑parameterized condition in its WHERE clause that the join index covers.

For example, suppose you create the following base table and single‑table join index:

     CREATE TABLE tp1 (
      pid     INTEGER,
      name    VARCHAR(32),
      address VARCHAR(32),
      zipcode INTEGER);
      SELECT pid, name, zipcode 
      FROM tp1
      WHERE zipcode >50000 
      AND   zipcode < 55000;