Re: Risky enable star transformations and trusted Query Rewrites?

ronnie.bahlsten_at_varchar.se wrote:
> Hi,
>
> I need some advice/opinions from someone experienced with large scale
> data warehousing.
>
> I'm working on a fairly large data warehouse (around 3 TB), and we're
> using Oracle 10.1.0.2.0.
>
> So, I found out about MV's and Star Transformations, and that we're not
> using them.
> Naturally I decided to try them out in our test environment and I was
> more than pleased (actually, I nearly wet my pants) with the potential
> performance boost we could get for some of our more critical solutions.
>
> However, I also noticed that the production environment has the
> following settings:
> star_transformation_enabled = false
> query_rewrite_integrity = enforced
>
> ...which basically disables all the cool stuff. In the testing
> environment I used the following:
> star_transformation_enabled = true
> query_rewrite_integrity = trusted (to make use of func. dep in
> dimensions)
>
>
> I would like to stand on somewhat solid grounds and increase my
> understanding before aproaching our DBA's with the suggestion to change
> system global settings :)
>
> Basically, my question(s) are:
>
>
> 1. What are the impact of enabling Star Transformations on a system?
> Is there any at all, if no previous solution has been built in a way
> to
> make use of star transformations?
> Or could this change result in fine-tuned queries performing badly
> since they
> suddenly make use of star transformations?
>
> 2. Is "query_rewrite_integrity" used by Oracle for other things besides
> Materialized Views?
> I'm thinking, if the only thing it's used for is to resolve query
> rewrites for MV's, then it's safe to change it, because there are no
> such MV's.
> Note that I'd like to set it to TRUSTED, in order to make real use
> of the dependencies declared with CREATE DIMENSION...
>
>
> I would be happy to know what you think about this.
> Any thoughts, opinions are welcome since this is new grounds for me.
>
> Best Regards
> R.

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 - 06:55:54 CST