Simple Join Indexes | Database Design | VantageCloud Lake - Simple Join Indexes - Teradata Vantage

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 called 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 can 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 may 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

You can also use a join index to partially cover a query to improve query performance. For example, if you want to count the number of orders made by customers in the European region during October, you can 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. Vantage can still use the join index to partially cover the query by joining the contents of the join index with the location table.

See When Join Indexes Are Useful for more information about partial query coverage.