15.00 - Simple Join Index Example - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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