Star Schema and partitioned views

We are using a Star Schema with Oracle 7.3.2 and partitioned views.

The tables in the view are partitioned using a TIME_PERIOD which is defined as DATE. There are several months in the partitioned view.

The FACT table has a compound key which includes TIME_PERIOD.

We join the FACT table to a dimension table with a primary key of TIME_PERIOD. An attribute of the TIME dimension table is CURRENT_MONTH which can have a value of 'Y' or ''N' which allows us to easily use the "relative" attribute of what the current month is.

So a typical query is:

select company, sum(sales)
from fact_table
where fact_table.time_period=time_dimension.time_period

      and time_dimension.current_month = 'Y' group by company

The problem is that the CHECK on the partitioned view is on the DATE field and all the tables in the view are scanned which kills performance. If we have the time_period in the WHERE clause it works and only the "current" month table is read.
(the time_period column from the time_dimension table OR the fact_table will work)

select company, sum(sales)
from fact_table
where fact_table.time_period=time_dimension.time_period

      and time_dimension.time_period = 'APR-03-1997' group by company

Anyone have any suggestions on how to get the partioned view to recognize the first select example and not to scan all the tables ... without having to create a current_month attribute in the FACT table and using a CHECK on it.

--
---------------------------------------------------
Robert Risholm
http://www.j51.com/~risholmr