When you enter a query, the Optimizer determines if accessing j1 gives the correct answer and is more efficient than accessing the base tables. This Optimizer selects this sparse join index only for queries restricted to data from the year 2000. For example, a query may require data from June of 2000. Because the join index j1 contains all of the data for year 2000, the Optimizer may use the index to satisfy the following query:
SELECT store_id, dept_id, SUM(sales_dollars) AS sum_sd FROM sales WHERE sales_date >= 6/1/2000 AND sales_date < 7/1/2000 GROUP BY storeid, dept_id;
As another example, the following DDL creates a join index containing only those customers living in four western states of the US:
CREATE JOIN INDEX westcust AS
SELECT cust.id, cust.address, donations.amount
FROM cust, donations
WHERE cust.cust_id = donations.cust_id
AND donations.d_date > '2003/01/01'
AND cust.state IN ('CA', 'OR', 'WA','NV');
The Optimizer can use this join index for the following query written to find all donors from California with donations of 1,000 USD or more made since January 1, 2003.
SELECT custid, cust.address FROM cust, donations WHERE donations.amount > 1000 AND cust.state = 'CA' AND donations.d_date > '2003/01/01';
Because the customers and donations considered by the query are part of the subset included in the join index, the Optimizer uses the index to answer the query. This can save significant time, especially when the base tables are large, but queries typically look only at subsets of the tables.
Maintenance of sparse join indexes can be much faster than maintenance for other join indexes because the sparse types have fewer values, and so are updated much less frequently.
See Sparse Join Indexes and Tactical Queries for information about design issues specific to sparse join index support for tactical queries.