RE: star_transformation_enabled performance question

That would be an interesting project. And that's really what I was kind of getting at with the question on the star transformation. Many have a pretty good feel for when, under what conditions, characteristics of data, etc certain techniques and approaches work or don't work well. And in just the very limited testing on this DB I haven't had a chance to dig into the details and characteristics and start to draw such conclusions (and then further test those conclusions). Some I know simply make it a policy to have it enabled but always use the TEMP_DISABLE option.

And your comments about the very restricted results set is probably the first time I've come across that comment and something I've kicked around. In the case in which I was testing (and it was pretty limited) the fact table is just a hair over 1 billion rows with around 35-40 million per monthly partition and an average row size of 620 (yeah, they've poked more than just a few measures in there ;-)). The queries, which were aggregates, would have ranged anywhere from a single month to a quarter and on up to a year. Only 2 to 3 dimensions used in the join, and pretty restrictive criteria on those themselves but could each result in various amounts of rows in the fact. I would have to go back and check the numbers but typically it would have resulted in aggregating anywhere from just a few thousand on up to maybe 2 to 5 million (which would have then fed back into the dimensions using hj's). One interesting thing to do would be to take the same query, one version that selects only measures from the fact, and another that includes output from the dimensions, thus requiring the join back. Could give some insight into where the effort is being spent.

I've worked on other systems where it was enabled, and in some cases where the TEMP_DISABLE option was used. But because things worked well, there was never the pressing need (not to imply no desire) to better understand the characteristics.

Along those same lines, this particular DB is the only one where we've had to set _index_join_enabled to FALSE. Had a lot of queries going South using the feature and simply had to turn off for immediate relief but have never had the time to be able to dig into the why's of the situation. Something else we intend to revisit when time permits, especially since this is the only case I've seen where it was better to disable the feature. So, a couple of areas and features where we need to dig in and understand the characteristics of what makes them work, or not work, well.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jonathan
> Lewis
> Sent: Saturday, April 19, 2003 4:17 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: star_transformation_enabled performance question
>
>
>
> One of the problems I find with writing up
> experiences and experiments is that the
> effects are often bounded by the particular
> data set that you happen to use.
>
> So far I've had very good results with
> star transformation, but that maybe that's
> because I've always been looking at it in
> situations where it happens to be the best
> thing to do.
>
> It would be interesting to start a project to
> categorise data sets where different features
> perform particularly badly, or particularly well -
> using real data, rather than trying to guess
> what people might do.
>
> The star transformation is particularly effective
> when the conditions against the dimension
> tables identify a very restricted set of rows in
> the fact table - I imagine there are cases where
> it would perform quite badly if the quantity of
> data ultimately targeted in the fact table was
> quite large, leading to a very expensive join-back.
> Might that be relevant in your case ?
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).