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 -> Risky enable star transformations and trusted Query Rewrites?

Risky enable star transformations and trusted Query Rewrites?

From: <ronnie.bahlsten_at_varchar.se>
Date: 27 Nov 2006 03:19:06 -0800
Message-ID: <1164626346.404918.159590@l12g2000cwl.googlegroups.com>


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. Received on Mon Nov 27 2006 - 05:19:06 CST

Original text of this message

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