-
We have Oracle 8i runing on Sun Solaris 2.8.
1. star_transformation_enabled=true
2. We have bitmap indexes on all FK columns
3. All tables have been analyzed
4. query_rewrite_enable=true
I do not know why Oracle optimizer does not do star-transformation for star-query.
I want to know to force optimzer to do
star-transformation. Or do you have any suggestion to improve the performance of this query?
The current execution plan shows it uses hash joins to join the fact table and all dimension tables.
Star Query looks like:
select rp.name,
days.month_id,
prod.pname,
cust.vip,
count(distinct bbd.actnum),
count(distinct bbd.sid),
sum(bbd.billamount),
from BILL_FACT bbd, DIM1 rp, days, DIM2 prod, DIM3cust
where bbd.actnum = cust.actnum and
bbd.day_id = days.day_id and
bbd.rpc = rp.rpc and
bbd.eid = prod.eid
group by vip, name, month_id, pname
order by month_id, vip, pname, name
Thank you!
-
Add a hint in the query /+STAR+/, then run the explain plan.
