Making CBO detect STAR queries

I'm currently working on a datawarehouse application and would like to speed up queries by using star execution plans.

We use Oracle 8.0.5 Enterprise Edition and an OLAP tool that creates our queries. Since this tool doesn't allow to generate hints into the sql code, we would like to know how to make the cost based optimizer detect star queries.

I studied oracle's documentation about star queries and the corresponding execution plans.

I used EXPLAIN PLAN to get execution plans of typical star queries but they yielded in classical execution plans i.e. joining our 3 meg line fact table iteratively to the small dimension tables instead of first joining the dimension table to each other and then joining the result to the fact table.

We analyzed our tables with 'ESTIMATE' at present, maybe 'COMPUTE ... FOR ALL INDEXED COLUMNS' will add more detail.

We tried the STAR query hint which resulted in a star execution plan but, as stated above, this is not possible in our production environment since the olap tool won't generate this hint.

Any help is appreciated.

Thanks,

Robert. Received on Wed Aug 25 1999 - 03:11:16 CDT