RE: Optimization of Partitioned Outer Joins

Hi Jonathan

>It would be a little unusual to access a very
>large fact table from just one dimension table.
>
>Imagine joining 4 dimensions to the fact
>table - and then wanting to produce a
>report which actually did what the
>partitioned outer join was designed to
>do - i.e. show the gaps with zeros.
>
>You either have to construct the
>query very carefully (much as you
>have done with your single dimension)
>or you have to make every join in
>sight an outer join so that the joins
>from the other three dimensions do
>not eliminate the rows generated by
>the partition outer join.

I agree with... anyway two remarks:

1. Even if in my sample queries I used a star schema, I'm not testing = the feature only in a DWH environment.
2. In a DWH environment it's capital that the feature supports the star = transformation. Otherwise "sub-optimal" response time is almost sure... = For this reason in the next days I'll test partitioned outer join with = the star transformation. Eventually, like for some restrictions of BJI, = with a star transformation the restriction is not important.

>It may be that your example simply falls
>into a special degenerate case that could
>be addressed with some custom code (in
>the optimizer), but that's the sort of thing
>that tends to get addressed a few minor
>releases down the line.

Once more I agree with you... anyway for me it's interesting to know the = limits of a new feature. For this reason I'm doing these tests and I'm = documenting the results in the 10g course I'm writing right now. (I'm = sure you do the same in your courses!)

Chris

--
http://www.freelists.org/webpage/oracle-l