FreeLists / oracle-l / RE: Optimization of Partitioned Outer Joins

["Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>]

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