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.
For example. a query might require data from departments 42, 43, and 44, but only for the year 2007. Because the join index j1 contains all of the data for year 2007, it might 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;
Be aware of the following complication: 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. This means that character values that pass a CHECK constraint for one session collation might fail the same check when a different session collation is in effect. This can have a particularly important effect on the values contained in a sparse join index, which, in turn, can affect the result of a request that the Optimizer uses that index to cover. Furthermore, such a scenario can also affect the result of the same request submitted when the sparse join index exists versus the same request submitted for the identical data when that sparse join index does not exist.
See Teradata Vantage™ - Database Design, B035-1094 for applications of sparse join indexes that take advantage of a partitioned primary index on the base table they support.