Re: Making CBO detect STAR queries

Hi Robert,

Can you get to the SQL your OLAP generates before it is executed and add the hint? If so the short term solution is to edit the SQL before it is executed. If not, and your queries would run a very long time, is it practical to let them start executing, kill the query and then edit the SQL statement and restart it? (Yes, this is not my idea of fun, but if it means cutting a four or five hour query down to four or five minutes ...).

In order to come up with a long term solution one thing I would do if I were you is complain to the vendor of your OLAP tool. If they are generating different code for each of the RDBMS they are supporting it shouldn't be very difficult for them to add the hint to their queries.

regards
Jerry Gitomer

Robert Graf-Waczenski wrote in message
<37C3A523.A92D7530_at_med.siemens.de>...
>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 - 13:22:26 CDT