Using the BETWEEN Clause to Reduce Row Redistribution
When retrieving data for a time interval, you can use MIN and MAX dates to define a BETWEEN clause, which results in fewer row redistributions than other methods.
Given a reference calendar that contains 730 rows with:
If you want summary data from a History table for fiscal_quarter, the standard query would be:
SELECT H.item_code, SUM(H.items_sold), SUM(H.sales_revenue)
FROM History H , Calendar C
WHERE C.fiscal_quarter = '3Q06'
AND C.calendar_date = H.sale_date
GROUP BY H.item_code
ORDER BY H.item_code;
The Optimizer processes the query as follows:
1 Build a spool table with dates from the reference calendar (90 days).
2 Duplicate the calendar spool. Either:
Alternatively, redistribute the entire History table. Product join the large table with the calendar spool (~1 row /AMP).
Another approach is to denormalize the History table. Add fiscal_week, fiscal_month, fiscal_quarter to the History table. Qualify fiscal_month directly in the denormalized table. The penalties for using this approach include: