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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____USA_(FL)_May 2nd
____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 19 April 2003 13:56

> Listers,
>
> 8.1.7.4 64 Bit Solaris 8. Looking at a database where
> "_star_transformation_enabled "(now a hidden underscore parameter)
is set to
> false. When asking about the history of why this is set to false, I
was told
> they had issues with the TEMP tables that were created during the
execution
> phase. Did an alter session setting it to true and ran some tests on
some
> good candidates, watched the CBO choose the star transformation, and
in all
> but one case the star transformation plan was much slower. Changed
to
> TEMP_DISABLE to not use TEMP tables, and things still weren't so
good. I'm
> planning on doing some more testing, getting a delta on session
stats,
> monitoring TEMP segments, etc.
>
> There will be an upgrade to 9i sometime in the near future. Has
anyone
> noticed substantial (or even minor) differences in the behavior of
star
> transformations? Maybe better handling of memory and temp tables?
And yes, I
> just finished re-reading Jonathan Lewis's article on the subject
> (http://www.dbazine.com/jlewis6.html) and am googling for others
> experiences. FWIW, we have some aggregates that due to the nature of
the
> queries and the app utilize multi-column unique b-tree indexes in
addition
> to standalone BMI's. STAR plans doing a Cartesian on the dimensions
and then
> diving in on the multi-column key perform very well (though the CBO
isn't
> quite aggressive enough when deciding to use a STAR plan, at least
for some
> of our cases).
>
> Anyway, any thoughts or experiences on the topic are welcome.
>
> Regards,
>
> Larry G. Elkins
> elkinsl_at_flash.net
> 214.954.1781
>
> --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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).