Sparse Join Indexes - Teradata VantageCloud Lake

You can create join indexes that restrict the number of rows in the index to those that are accessed when, for example, a frequently run request references only a small, well known subset of the rows of a large base table. By using a constant expression in the WHERE clause to filter the rows included in the join index, you can create what is known as a sparse join index.

For example, the following CREATE JOIN INDEX request creates an aggregate join index containing only the sales records from 2007. The filtering is done by the constant predicate expression EXTRACT(year, sales_date)=2007 in the WHERE clause.

    CREATE JOIN INDEX j1 AS
    SELECT storeid, deptid, SUM(sales_dollars)
    FROM sales
    WHERE EXTRACT(year, sales_date)=2007
    GROUP BY storeid, deptid;

When a request is made against an indexed base table, the Optimizer determines if accessing j1 provides the correct answer and is more efficient than accessing the base tables. The Optimizer then selects this sparse join index only for queries that restricted themselves to data from the year 2007.

Suppose a query requires data from departments 42, 43, and 44, but only for the year 2007. Because the join index j1 contains all data for year 2007, it can be used to satisfy the following query.

    SELECT storeid, deptid, SUM(sales_dollars)
    FROM sales
    WHERE EXTRACT(year FROM sales_date) = 2007
    AND deptid IN(42, 43, 44)
    GROUP BY storeid, deptid;

When a base table has a CHECK constraint on a character column, values inserted into or updated for that column are tested based on the collation for the current session. Character values that pass a CHECK constraint for one session collation can fail the same check with a different session collation. This difference can affect the values in a sparse join index, which can affect the result of a request for which the Optimizer uses that index. Also, whether the index exists when the request is submitted can affect its result.

See Sparse Join Indexes for applications of sparse join indexes that take advantage of a partitioned primary index on the base table they support.