Re: Optimization of Partitioned Outer Joins

I think the answer is 'generic use'.

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.

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.

Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Dec 23rd 2004






----- Original Message ----- 
From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>

3) the modified query (TIMES is joined once more to enable an index access)

SELECT t.fiscal_month_number,
       nvl(sum(s.amount_sold),0) amount_sold
FROM sales s JOIN (
               SELECT time_id
               FROM times
               WHERE fiscal_year = 1998
               AND fiscal_quarter_number = 2
             ) t1 USING (time_id)
             PARTITION BY (channel_id)
             RIGHT OUTER JOIN (
               SELECT time_id, fiscal_month_number
               FROM times
               WHERE fiscal_year = 1998
               AND fiscal_quarter_number = 2
             ) t USING (time_id)
WHERE channel_id = 9
GROUP BY t.fiscal_month_number;

--------------------------------------------------------------------
| Id  | Operation                              | Name              |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                   |
|   1 |  SORT GROUP BY                         |                   |
|   2 |   VIEW                                 |                   |
|   3 |    MERGE JOIN PARTITION OUTER          |                   |
|   4 |     SORT JOIN                          |                   |
|   5 |      VIEW                              |                   |
|*  6 |       TABLE ACCESS FULL                | TIMES             |
|*  7 |     SORT PARTITION JOIN                |                   |
|   8 |      VIEW                              |                   |
|   9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |
|  10 |        NESTED LOOPS                    |                   |
|* 11 |         TABLE ACCESS FULL              | TIMES             |
|  12 |         PARTITION RANGE ITERATOR       |                   |
|  13 |          BITMAP CONVERSION TO ROWIDS   |                   |
|  14 |           BITMAP AND                   |                   |
|* 15 |            BITMAP INDEX SINGLE VALUE   | SALES_TIME_BIX    |
|* 16 |            BITMAP INDEX SINGLE VALUE   | SALES_CHANNEL_BIX |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("FISCAL_YEAR"=1998 AND "FISCAL_QUARTER_NUMBER"=2)
   7 - access("T"."TIME_ID"="from$_subquery$_004"."TIME_ID")
       filter("T"."TIME_ID"="from$_subquery$_004"."TIME_ID")
  11 - filter("FISCAL_YEAR"=1998 AND "FISCAL_QUARTER_NUMBER"=2)
  15 - access("S"."TIME_ID"="TIME_ID")
  16 - access("S"."CHANNEL_ID"=9)

This query is much faster (ca. factor 5) and does much less LIO (ca. factor 
3.5). Of course in this case there is no big difference.... but in a real 
star schema it will be a real problem...



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

Other related posts: