Try to define a descending index on CAL_DATE.

Regards,
Aage J.


Bob Murdoch wrote:

>
> 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
> select
> fiscal_week, fiscal_month, fiscal_season, fiscal_year, max(cal_date)
> from
> calendar
> 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
> clause.
>
> 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.