Sparse Join Indexes
Any join index, whether simple or aggregate, multitable or single-table, can be sparse. A sparse join index specifies a constant expression in the WHERE clause of its definition to narrowly filter its row population. For example, the following DDL creates an aggregate join index containing only the sales records from 2000:
CREATE JOIN INDEX j1 AS
SELECT store_id, dept_id, SUM(sales_dollars) AS sum_sd
FROM sales
WHERE EXTRACT(year FROM sales_date) = 2000
GROUP BY store_id, dept_id;
This method limits the rows included in the join index to a subset of the rows in the table based on an SQL request result.
When base tables are large, you can use this feature to reduce the content of the join index to only the portion of the table that is frequently used if the typical query only references a portion of the rows.
It is important to collect statistics on the sparse-defining column of the join index, or the Optimizer may not use the join index.