16.10 - Simple Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
Release Number
Release Date
June 2017
Content Type
User Guide
Publication ID
English (United States)

The primary function of a simple join index is to provide the Optimizer with a high-performing, cost-effective means for satisfying any query that specifies a frequently performed join operation. The simple join index permits you to define a permanent prejoin table without violating the normalization of the database schema. Simple join indexes are also referred to as multitable join indexes.

Simple Join Index Example

For example, suppose that a common task is to look up customer orders by customer number and date. You might create a join index like the following, linking the customer table, the order table and the order detail table:

    CREATE JOIN INDEX cust_ord2
    AS SELECT cust.customerid,cust.loc,ord.ordid,item,qty,odate
    FROM cust, ord, orditm
    WHERE cust.customerid = ord.customerid
    AND ord.ordid = orditm.ordid;

While you might never issue a query that completely joined these three tables, the key benefit of this join index is its versatility. For example, a query that only looks at the customers for a single state, like the following, can still use the cust_ord2 join index rather than accessing its underlying base tables.

    SELECT cust.customerid, ord.ordid, item, qty
    FROM cust, ord, orditm
    WHERE cust.customerid = ord.customerid
    AND ord.ordid = orditm.ordid
    AND cust.loc = 'WI';

Simple Join Indexes and Partial Query Covering

It is also possible for a join index to be used to partially cover a query to improve query performance. For example, if you wanted to count the number of orders made by customers in the European region during October, you might use the following query:

    SELECT cust.customerid,COUNT(ord.ordid)
    FROM cust, ord, orditm, location
    WHERE ord.ordid = orditm.ordid
    AND cust.customerid = ord.customerid
    AND cust.loc = location.loc
    AND location.region = 'EUROPE'
    AND EXTRACT(MONTH, ord.orddate) = 10
    GROUP BY cust.customerid;

In this example, the query includes the location table which is not included in the join index. Teradata Database can still use the join index to partially cover the query by joining the contents of the join index with the location table.

See Partial Query Coverage for more information about partial query coverage.