oracle 9i star_transformation

HI

I`ve made some time ago a report using oracle. I had fact table and a couple of dimensions (including time dimension). Now this report when I want to refresh this report I cannot use star_transformation. (even when I use star_transformation hint)

my sql is sth like this:
smtm1:
select /*+ star_transformation */......
from fact, dim1, dim2, dim3, time_dim, .... where
...
time_dim = to_date(...)
group by dim`s

and for this statement plan looks terribly but when I rewrite this sql as
stmt2:
select count(*) from (
**** above statement stmt1 ****
)
plan looks very good (using transformation) and works fastly when I throw away time dimension from stmt1 and use time from fact_table
(which is key to time_dimension) then statement looks like:
smtm3:
select /*+ star_transformation */......
from fact, dim1, dim2, dim3, time_dim, .... where
...
/*time_dim = to_date(...)*/
fact.time = to_date(...)
group by dim`s

everything works fine and star_transformation is used.

every dim_id (number) from fact table is indexed (bitmap) and time_id
(date) in fact table is also indexed (bitmap)

why my connection to time_dimension does not work (it worked in the past).
all of statistics are present.

thx. for answers.
linuxpld Received on Thu Oct 27 2005 - 13:17:25 CDT