15.00 - Simple Join Indexes and Partial Query Covering - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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” on page 505 for more information about partial query coverage.