DSS star transformation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: DSS star transformation

  1. #1
    Join Date
    Jun 2000
    Posts
    295
    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!


  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Add a hint in the query /+STAR+/, then run the explain plan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width