Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Risky enable star transformations and trusted Query Rewrites?

Re: Risky enable star transformations and trusted Query Rewrites?

From: <ronnie.bahlsten_at_varchar.se>
Date: 27 Nov 2006 05:51:55 -0800
Message-ID: <1164635515.846117.221860@45g2000cws.googlegroups.com>


Hi Charles,

thanks for taking the time.

I haven't seen any of the behaviour you described from my tests, so I guess it has been fixed.
8.1.7.3 was some time ago *shrugs*.

The section you're quoting is the reason I thought it would be OK for me change the setting, since none of the existing solutions use bitmap indices on the join keys. This made me think that only new solutions setup according to the guidelines would be affected. In fact, I had to spend some time before the transformations kicked in at all.
It helped when I read the following my Jonathan Lewis http://www.dbazine.com/oracle/or-articles/jlewis6

Good to know about the bind variables!
But it doesn't have any impact in this particular situation since all queries are generated by the reporting software (no binds there).

Again, thanks for taking the time to help out!

Best Regards
Ronnie

Charles Hooper skrev:

> I experimented with the star_transformation_enabled = true setting
> under Oracle 8.1.7.3 with unfavorable results - totals from some
> reports were incorrect, detail lines were missing, etc. I am certain
> that a lot has changed from 8.1.7.3 to 10.1.0.2.0.
>
> Quoting from:
> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14223/schemas.htm
>
> "To get the best possible performance for star queries, it is important
> to follow some basic guidelines: A bitmap index should be built on
> each of the foreign key columns of the fact table or tables." "A
> prerequisite of the star transformation is that there be a
> single-column bitmap index on every join column of the fact table.
> These join columns include all foreign key columns."
>
> Also see the section titled "Star Transformation Restrictions"
> If the queries contain bind variables (following good programming
> techniques) star transformations are not supported.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Mon Nov 27 2006 - 07:51:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US