Simple Join Index Example - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

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';