Subject Max() in view does not use index
Author Bob Murdoch
I have a Calendar table, which contains all dates for a twenty year time
span. The table looks like this:

create table Calendar (
cal_date date,
fiscal_week integer,
fiscal_month integer,
fiscal_season integer,
fiscal_year integer)

I have seperate indexes on cal_date, fiscal_season, and fiscal_year.

I'm creating a view to select the last date of each week for a season:

create view fiscal_saturday as
fiscal_week, fiscal_month, fiscal_season, fiscal_year, max(cal_date)
group by
fiscal_week, fiscal_month, fiscal_year

If I run the following query against the view, no indexes are used:
select * from fiscal_saturday where fiscal_season = 0 and fiscal_year
= 2002

I'm assuming that the way the view works is that it has to build it's
result set first, then filter that to match the restrictions of the where

My question is simply how can I speed this up? I actually need to join
this to another table for selecting some data, and the 15000 reads against
the calendar table are killing it's performance.


Bob M..