Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Risky enable star transformations and trusted Query Rewrites?
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